Study of SQL Queries

STUDY OF SQL QUERIES

DDL COMMANDS

DATA DEFINITION LANGUAGE The DDL commands are:• • • • •

Create table Alter table Drop table Create view Drop view

SHOPPING DETAILS: TABLE STUCTURE

1.

FIELD NAME

DATA TYPE

SIZE

ITEM NAME

ALPHANUMERIC

15

ITEM CODE

NUMBER

5

COST PRICE

NUMBER

5

SELLINGPRICE

NUMBER

5

CREATE COMMAND:-

 Syntax: Create table (column_name datatype (size) constrains); Description:

The create command when applied with above specification creates the field of  different data type. 2. ALTER COMMAND:-

 Syntax: a) alter table add (column_name datatype (size)); Description:

The alter command when used with add allows us to add an additional column to an already existing table.  Syntax: b) alter table < table_name> modify(column_name datatype(size));

Description:

The alter command when used with modify redefines the column with the given values but cannot change the column names.  Syntax: c)

alter table drop(column_name);

Description:

The alter command when used with drop deletes the specified column in the table. 3.

DROP COMMAND:-

 Syntax:  Drop table ; Description:

A table can be dropped (deleted) by using a drop table command. 4.

CREATE VIEW COMMAND:-

 Syntax: Createview as select from where ; Description:

A view is named, derived, virtual table. A view takes the output of a query and treats it as a table; a view can be thought of as a “stored query” or a “virtual table”. The tables upon which a view is based are called base tables. 5. DROP VIEW COMMAND:-

 Syntax:  Drop view ; Description:

A view can be dropped (deleted) by using a drop view command. 6. TRUNCATE COMMAND:-

 Syntax: Truncate table ; Description:

The details in the table are deleted but the table structure remains.

7. RENAME COMMAND:-

 Syntax:  Rename to ; Description:

The old table name is replaced with the new table name.

PROGRAM TO LEARN DDL COMMANDS CREATE TABLE:

SQL> create table lists(name varchar(14),code number(4),price number(3),quantity number(3)); Table created. SQL> desc lists; Name Null? Type ---------------------------------- -------- ---------------------------NAME VARCHAR(14) CODE NUMBER(4) PRICE NUMBER(3) QUANTITY NUMBER(3) ALTER TABLE:

SQL> alter table lists add(sellinprice number(4)); Table altered. SQL> desc lists; Name Null? Type ---------------------------------- -------- ---------------------------NAME VARCHAR(14) CODE NUMBER(4) PRICE NUMBER(3) QUANTITY NUMBER(3) SELLINPRICE NUMBER(4) SQL> alter table lists drop(sellinprice); Table altered. SQL> desc lists;

Name Null? Type ---------------------------------- -------- ---------------------------NAME VARCHAR (14) CODE NUMBER(4) PRICE NUMBER(4) QUANTITY NUMBER(3) CREATE VIEW:

SQL> create view listsview as select * from lists; View created. SQL> desc listsview;  

Name Null? Type ---------------------------------- -------- ---------------------------NAME VARCHAR(14) CODE NUMBER(4) PRICE NUMBER(4) QUANTITY NUMBER(3) SQL> select * from listsview;

 

NAME CODE PRICE QUANTITY ----------------------- ---------- ---------  powder 101 50 5 dhal 102 45 10 masala 103 20 12   powder 104 30 8 icecream 105 70 5   pen 106 10 20 icecream 107 40 5 7 rows selected. DROP VIEW:

SQL> drop view listsview; View dropped. SQL> desc listsview; ERROR: ORA-04043: object listsview does not exist.

TRUNCATE:

SQL> truncate table lists; Table truncated. SQL> desc lists;  

Name Null? Type ---------------------------------- -------- ---------------------------NAME VARCHAR(14) CODE NUMBER(4) PRICE NUMBER(4) QUANTITY NUMBER(3) RENAME:

SQL> rename lists to list; Table renamed. SQL> desc lists; ERROR: ORA-04043: object lists does not exist. SQL> desc list;  

Name Null? Type ---------------------------------- -------- ---------------------------NAME VARCHAR(14) CODE NUMBER(4) PRICE NUMBER(4) QUANTITY NUMBER(3) DROP TABLE:

SQL> drop table list; Table dropped. SQL> desc list; ERROR: ORA-04043: object lists does not exist.

DML COMMANDS

DATA MANIPULATION LANGUAGE The DML commands are:

• • •

Insert Delete Update

1.

INSERT :-

 Syntax :  Insert into values (val1,val2,…); Description:

The ‘insert into’ command insert the values in the specified table. In the insert into SQL sentence the column and values have a one to one relationship (i.e.) the first value described into the first column, the second value described being inserted into the second column and so on. 2. DELETE: Syntax:  Delete from where ; Description:

The delete in SQL is used to remove rows from table. To remove, 1. All the rows from a table. 2. A select set of rows from a table. 3. UPDATE: Syntax: Update set fieldname= where ; Description:

The update command is used to change or modify data values in a table. To update, 1. All the rows from a table. 2. A select set of rows from a table.

PROGRAM TO LEARN DML COMMANDS INSERTION:

SQL> insert into lists values('&name',&code,&price,&quantity) Enter value for name: powder  Enter value for code: 101 Enter value for price: 50 Enter value for quantity: 5 old 1: insert into lists values('&name',&code,&price,&quantity) new 1: insert into lists values('powder',101,50,5) 1 row created. SQL> insert into lists values ('dhal',102,45,10); 1 row created. SQL> insert into lists values ('masala`,103, 20,12) 1 row created. SQL> insert into lists values ('powder `104, 30,8) 1 row created. SQL> insert into lists values ('icecream`,105, 70,5) 1 row created. SQL> insert into lists values ('pen`,106, 10,20) 1 row created. SQL> insert into lists values ('icecream`,107,40,5) 1 row created. SQL> select * from lists;  

NAME -------------  powder dhal masala   powder icecream   pen icecream

CODE PRICE QUANTITY ---------- ---------- --------------101 50 5 102 45 10 103 20 12 104 30 8 105 70 5 106 10 20 107 40 5

UPDATE COMMAND:

SQL> update lists set name='soap' where code=103;

1 row updated. SQL> select * from lists;  

NAME -------------  powder dhal soap   powder icecream   pen icecream

CODE PRICE QUANTITY ---------- ---------- --------------101 50 5 102 45 10 103 20 12 104 30 8 105 70 5 106 10 20 107 40 5

DELETE COMMAND:

SQL> delete from lists where code=106; 1 row deleted. SQL> select * from lists;  

NAME -------------  powder dhal soap   powder icecream   pen icecream

CODE PRICE QUANTITY ---------- ---------- --------------101 50 5 102 45 10 103 20 12 104 30 8 105 70 5 106 10 20 107 40 5

DQL COMMANDS

DATA QUERY LANGUAGE The DML commands are:  Select  Order by

  

Group by Having  Null

1. SELECT STATEMENT: Syntax: a) select from [where clause]; Description:

Select command is used to retrieve data from one or more tables or columns. The attributes list is a list of attributes name whose values are displayed by query. A missing where clauses indicate no condition on tuples selection. The condition is a Boolean expression that identifies the tuples to be retrieved by the query.  Syntax: b) select distinct from ; Description:

Display the distinct values from a table by eliminating the duplicate values. It  performs grouping of the specified fields when queried with distinct statement. 2. ORDER BY CLAUSE: Syntax: Select from order by ; Description:

Order by sorts the output of any specified column or expression. The order by clause must always have task in any select statement. The default order is ascending order. We should specify the keywords ‘desc’ if we wand it in descending order. 3. GROUP BY CLAUSE:  Syntax: Select from group by ; Description:

The group by clause specifies the grouping of function to appear in the select clause. So that the value resulting from group of tuples appear along with the values of grouping attributes are SUM, AVERAGE, MAX, MIN and COUNT. 4. HAVING CLAUSE: Syntax:

Select from where group by condition> having ; Description:

The ‘having’ clause is used to specify certain conditions on rows, retrieved by using group by clause. This clause should be preceded by a ‘group by’ clause. 5. NULL COMMAND: Syntax: Select NVL(substitution column,substitutied value) from where ; Description:

The NVL function helps in substituting the value in the null fields. But this function only displays the change and does not update the columns.

PROGRAM TO LEARN DQL COMMANDS SELECT STATEMENT:

SQL> select * from list;  

NAME CODE PRICE QUANTITY ----------------------- ---------- ---------  powder 101 50 5 dhal 102 45 10 masala 103 20 12   powder 104 30 8 icecream 105 70 5   pen 106 10 20 icecream 107 40 5 7 rows selected. 1) List

down the item whose price >50.

SQL> select * from list where price>50;  

NAME -------------icecream 2) List

CODE PRICE QUANTITY ---------- ---------- ---------105 70 5

down item name and item code.

SQL> select code, name from list; CODE -------------101 102 103 104 105 106 107

NAME ---------powder  dhal masala powder  icecream pen icecream

7 rows selected. SELECT-ORDER BY: 3) List

down the item price in ascending order.

SQL> select * from list order by price;

 

NAME -------------  pen masala

CODE PRICE QUANTITY ------------------- ---------106 10 20 103 20 12

 

powder icecream dhal   powder icecream

107 102 105

104 40 45 101 70

30 5 10 50 5

8

5

7 rows selected. 4) List

down the item price in descending order.

SQL> select * from list order by price desc;  

NAME CODE PRICE QUANTITY ----------------------- ---------- ---------icecream 105 70 5   powder 101 50 5 dhal 102 45 10 icecream 107 40 5   powder 104 30 8 masala 103 20 12   pen 106 10 20 7 rows selected. SELECT-GROUP BY:

SQL> select name, sum(price) from list group by name;  

   

NAME -------------dhal icecream masala

SUM(PRICE) ---------45 110 20 pen 10 powder 80

NULL COMMAND:

SQL> select code,NVL(name,'masala') from lss where code=103; CODE NVL(NAME,'MASALA' ---------- -------------103 masala

BETWEEN CLAUSE:

5) List down the item where price between 20 and 50.

SQL> select * from list where price between 20 and 50;  

NAME CODE PRICE QUANTITY ----------------------- ---------- ---------  powder 101 50 5 dhal 102 45 10 masala 103 20 12   powder 104 30 8 icecream 107 40 5

6) List down the item where price between 20 and 50. SQL> select * from list where price not between 20 and 50;  

NAME -------------icecream   pen

CODE PRICE QUANTITY ---------- ---------- ---------105 70 5 106 10 20

IN AND NOT IN PREDICATE:

SQL> select * from list where name in ('icecream');  

NAME -------------icecream icecream

CODE PRICE QUANTITY ---------- ---------- ---------105 70 5 107 40 5

SQL> select * from lists where name not in ('icecream');  

NAME CODE PRICE QUANTITY ----------------------- ---------- ---------  powder 101 50 5 dhal 102 45 10 masala 103 20 12   powder 104 30 8   pen 106 10 20

 TCL COMMANDS

TRANSACTION CONTROL LANGUAGE The TCL commands are:  Commit  Rollback   Save point

1.COMMIT: Syntax: set auto commit on; set auto commit off; commit; Description:

Commit commands tells the DBMS to make permanent changes made to temporary copies of the data updating the permanent database tables to match the updated temporary copies. 2.ROLL BACK: Syntax: rollback; Description:

Roll back tells the DBMS to undo any changes made to the DBMS after the most recent commit. 3.SAVE POINT: Syntax: savepoint ; Description:

Save point are like markers to divide a very lengthy transaction to smaller ones. They are used to identify a point in transaction to which we can later rollback. Thus savepoint is used in conjunction with rollback to rollback portions of the current transaction.

PROGRAM TO LEARN TCL COMMANDS ROLLBACK:-

SQL> select * from list;  

NAME --------------

CODE PRICE QUANTITY ---------- ---------- ----------

 

powder dhal masala

102 103

 

powder icecream   pen icecream

105 107

101 45 20 104 70 106 40

50 10 12 30 5 10 5

5

8 20

7 rows selected. SQL> delete from list where code=104; 1 row deleted. SQL> select * from list;  

NAME CODE PRICE QUANTITY ----------------------- ---------- ---------  powder 101 50 5 dhal 102 45 10 masala 103 20 12 icecream 105 70 5   pen 106 10 20 icecream 107 40 5 6 rows selected. SQL> rollback; Rollback completed. COMMIT:

SQL> select * from list;  

NAME CODE PRICE QUANTITY ----------------------- ---------- ---------  powder 101 50 5 dhal 102 45 10 masala 103 20 12   powder 104 30 8

icecream  

105 pen

icecream

107

70 106 40

7 rows selected. SQL> delete from list where code=106;

5 10 5

20

1 row deleted. SQL> commit; Commit complete. SQL> select * from list;  

NAME CODE PRICE QUANTITY ----------------------- ---------- ---------  powder 101 50 5 dhal 102 45 10 masala 103 20 12   powder 104 30 8 icecream 105 70 5 icecream 107 40 5 6 rows selected. SQL> rollback; Rollback complete. SQL> select * from list;

 

NAME CODE PRICE QUANTITY ----------------------- ---------- ---------  powder 101 50 5 dhal 102 45 10 masala 103 20 12   powder 104 30 8 icecream 105 70 5 icecream 107 40 5 6 rows selected. SAVE POINT:

SQL> savepoint s1; Savepoint created. SQL> update list set name='vennila' where code=102; 1 row updated. SQL> savepoint s2;

Savepoint created. SQL> update list set name='chocolate' where code=105; 1 row updated. SQL> select * from list;

 

NAME CODE PRICE QUANTITY ----------------------- ---------- ---------  powder 101 50 5 vennila 102 45 10 masala 103 20 12   powder 104 30 8 chocolate 105 70 5 icecream 107 40 5 6 rows selected. SQL> rollback to s2; Rollback complete. SQL> select * from list;  

NAME CODE PRICE QUANTITY ----------------------- ---------- ---------  powder 101 50 5 vennila 102 45 10 masala 103 20 12   powder 104 30 8 icecream 105 70 5 icecream 107 40 5 6 rows selected.

DCL COMMANDS

DATA CONTROL LANGUAGE The DCL commands are:  Grant  Revoke

1. GRANT: Syntax: Grant on to user; Description:

Grand gives specifies SQL statement access or individual data objects to a user or a group of users. 2. REVOKE: Syntax: Revoke on from user; Description:

Revoke removes specific SQL statement access previously granted on individual database objects from a user or group of users.

PREDICATES SQL> select * from itemlist; CODE ITEM ------------ --------1 Pen

PRICE --------20.5

2 3

Notebook Bat

70.75 99.99

1. ALL:  Syntax: Select *from table_name where column_name>ALL(column_ value); Example:

SQL> select *from itemlist where code>ALL(1); CODE -----------2 3

ITEM PRICE --------- --------Notebook 70.75 Bat 99.99

2. SOME:  Syntax: select column_name from table_name where col_name2 > some (select  column_name from table_name where column_name 3 =`value`); Example:

SQL> Select item from itemlist where code>some(select code from itemlist where  price=20.5); ITEM --------- Notebook  Bat 3. ANY:

 Syntax: select column_name from table_name where column_name > any (select  column_name from table_name where column_name=`value `); Example:

SQL> select item from itemlist where price>any(select price from itemlist where code=2); ITEM ---------Bat 4. LIKE:

 Syntax: Select * from table_name where column_name like(`value’); Example:

SQL> select * from itemlist where item like('P%'); CODE ITEM PRICE ----------- ---------- --------1 Pen 20.5 5. EXISTS:

 Syntax: select * from table_name where exists(select column_name from table_name where column_name=(`value`); Example:

SQL> select * from itemlist where exists(select icode from itemlist where icode=3); CODE -----------1 2 3

ITEM PRICE --------- --------Pen 20.5 Notebook 70.75 Bat 99.99

CONSTRAINTS 1. DOMAIN INEGRITY CONSTRAINTS:i.

NOT NULL:

 Syntax: Create table(field datatype)constraintnot null…);

SQL> create table details(no number(3),name varchar(10),salary numeric(4,2) constraint sal not null); Table created. SQL> desc details; Name Null? ------------- ------------NO NAME SALARY NOT NULL

Type ------------NUMBER(3) VARCHAR2(10) NUMBER(4,2)

ii. CHECK:

SQL> create table employee (empid number(3),empname varchar(10),salary number(8,2) constraint salc check (salary>1000)); Table created. SQL> desc employee; Name Null? ------------------ -------EMPID EMPNAME SALARY

Type -------------NUMBER(3) VARCHAR2(10) NUMBER(8,2)

2. ENTITY INTEGRITY CONSTRAINTS:i.

PRIMARY KEY:

 Syntax: create table table_name( field datatype) constraints primary key (column_name);

SQL> create table note(id number(6)constraint flo not null, num number(6) primary key); Table created. SQL> desc note; Name Null? Type ----------- ---------------------ID NOT NULL NUMBER(6) NUM NOT NULL NUMBER(6) SQL>create table micky(id number(5),name varchar(6)constriants mouse primary key(id,name));

Table created  

Name Null? Type ----------- ---------------------ID NUMBER(5) NAME VARCHAR(6) ii.

UNIQUE:

 Syntax: create table table_name( field datatype) constraints unique (column_name);

SQL>create table jeep(id number(5)constriants van unique); Table created SQL>desc jeep;  

Name Null? Type ----------- ---------------------ID NUMBER(5) SQL>insert into jeep(&id); Enter the value for id:4 1 row created SQL>insert into jeep values(&id); Enter the value for id:4 Error at line1:] 3. REFERENCE INTEGRITY MODEL: REFERENCE:

 Syntax: create table table_name(data types..) constraint from references table_name(column_name);

SQL>create table jeep(id number(5)constraint from references jeep(id)); Table created. SQL>desc jeep;

 

Name Null? Type ----------- ---------------------ID NUMBER(5)

RESULT:-

Thus the various SQL QUERIES were studied and executed successfully.