DBMS Questions and Answers

1. What is database?                                                                                            A database...

Read more ...

Real time Queries for SQL

Create emp table in your Schema. Insert 10 values, Then try bellow queries. How to retrive the second record in the table(sql)? (select rownum, e.* from emp e WHERE ROWNUM < 3) MINUS (select rownum, e.* from emp e WHERE ROWNUM < 2) ; How to find out the records except first record in the table(sql)? (select rownum, e.* from emp e) MINUS (select rownum,e.* from emp e WHERE ROWNUM < 2) ; or select * from...

Read more ...

Req: How to attach the empno number before predefined value in a particular column in employee table? Using Sequence .....

Create new table, or other wise copy existing table. Then Create Sequence. While Inserting data into database using bellow insert query. Every time empno before added " PREDIFINEDVALUE00 ". ex: PREDIFINEDVALUE001 PREDIFINEDVALUE002 PREDIFINEDVALUE003 ... etc. Queries: CREATE TABLE EMP_ONE AS SELECT * FROM EMP; CREATE SEQUENCE EMP_ONE_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH 1 INCREMENT BY...

Read more ...

Oracle/PLSQL: Synonyms

A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects. Creating or replacing a synonym The syntax for creating a synonym is: create [or replace]  [public]  synonym [schema .] synonym_name for [schema .] object_name [@ dblink]; The or replace phrase allows you to recreate the synonym (if it already exists) without having to issue a DROP...

Read more ...

SQL: VIEWS

A view is, in essence, a virtual table. It does not physically exist. Rather, it is created by a query joining one or more tables. Creating a VIEW The syntax for creating a VIEW is: CREATE VIEW view_name AS SELECT columns FROM table WHERE predicates; For Example: CREATE VIEW sup_orders AS SELECT suppliers.supplier_id, orders.quantity, orders.price FROM suppliers, orders WHERE suppliers.supplier_id = orders.supplier_id and...

Read more ...

SQL: Data Types

The following is a list of general SQL datatypes that may not be supported by all relational databases. |-----------+------------+--------------------------------------| | Data Type | Syntax | Explanation (if applicable) | |-----------+------------+--------------------------------------| | integer | integer | | |-----------+------------+--------------------------------------| |...

Read more ...

SQL: COUNT Function

The COUNT function returns the number of rows in a query. The syntax for the COUNT function is: SELECT COUNT(expression) FROM tables WHERE predicates; Note: The COUNT function will only count those records in which the field in the brackets is NOT NULL. For example, if you have the following table called suppliers: |-------------+----------------+------| | Supplier_ID | Supplier_Name | State| |-------------+----------------+------| |...

Read more ...