Download - Study of SQL Queries
-
8/8/2019 Study of SQL Queries
1/29
STUDY OF SQL
QUERIES
-
8/8/2019 Study of SQL Queries
2/29
DDL COMMANDS
-
8/8/2019 Study of SQL Queries
3/29
DATA DEFINITION LANGUAGE
The DDL commands are:-
Create table Alter table Drop table Create view Drop view
SHOPPING DETAILS:
TABLE STUCTURE
FIELD NAME DATA TYPE SIZE
ITEM NAME ALPHANUMERIC 15ITEM CODE NUMBER 5COST PRICE NUMBER 5
SELLINGPRICE NUMBER 5
1. 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 analready existing table.
Syntax:b) alter table < table_name> modify(column_name datatype(size));
Description:
-
8/8/2019 Study of SQL Queries
4/29
The alter command when used with modify redefines the column with the givenvalues 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 treatsit as a table; a view can be thought of as a stored query or a virtual table. The tables uponwhich 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.
-
8/8/2019 Study of SQL Queries
5/29
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),quantitynumber(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;
-
8/8/2019 Study of SQL Queries
6/29
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 5dhal 102 45 10masala 103 20 12
powder 104 30 8icecream 105 70 5
pen 106 10 20icecream 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.
-
8/8/2019 Study of SQL Queries
7/29
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.
-
8/8/2019 Study of SQL Queries
8/29
DML COMMANDS
DATA MANIPULATION LANGUAGE
The DML commands are:
-
8/8/2019 Study of SQL Queries
9/29
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 insertinto SQL sentence the column and values have a one to one relationship (i.e.) the first valuedescribed into the first column, the second value described being inserted into the secondcolumn 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:
-
8/8/2019 Study of SQL Queries
10/29
SQL> insert into lists values('&name',&code,&price,&quantity)Enter value for name: powder Enter value for code: 101Enter value for price: 50Enter 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 CODE PRICE QUANTITY
-------------- ---------- ---------- --------------- powder 101 50 5dhal 102 45 10masala 103 20 12
powder 104 30 8icecream 105 70 5
pen 106 10 20icecream 107 40 5
UPDATE COMMAND:
SQL> update lists set name='soap' where code=103;
-
8/8/2019 Study of SQL Queries
11/29
1 row updated.
SQL> select * from lists;
NAME CODE PRICE QUANTITY-------------- ---------- ---------- ---------------
powder 101 50 5dhal 102 45 10soap 103 20 12
powder 104 30 8icecream 105 70 5
pen 106 10 20icecream 107 40 5
DELETE COMMAND:
SQL> delete from lists where code=106;
1 row deleted.
SQL> select * from lists;
NAME CODE PRICE QUANTITY-------------- ---------- ---------- ---------------
powder 101 50 5dhal 102 45 10soap 103 20 12
powder 104 30 8icecream 105 70 5
pen 106 10 20icecream 107 40 5
-
8/8/2019 Study of SQL Queries
12/29
DQL COMMANDS
DATA QUERY LANGUAGE
The DML commands are:
SelectOrder by
-
8/8/2019 Study of SQL Queries
13/29
Group byHaving
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. Theattributes list is a list of attributes name whose values are displayed by query. A missingwhere clauses indicate no condition on tuples selection. The condition is a Booleanexpression 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 clausemust always have task in any select statement. The default order is ascending order. Weshould 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 groupingattributes are SUM, AVERAGE, MAX, MIN and COUNT.
4. HAVING CLAUSE:-
Syntax:
-
8/8/2019 Study of SQL Queries
14/29
Select from where group bycondition> having ;
Description:
The having clause is used to specify certain conditions on rows, retrieved by usinggroup 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 functiononly displays the change and does not update the columns.
PROGRAM TO LEARN DQL COMMANDS
SELECT STATEMENT:
-
8/8/2019 Study of SQL Queries
15/29
SQL> select * from list;
NAME CODE PRICE QUANTITY-------------- ---------- ---------- ----------
powder 101 50 5dhal 102 45 10masala 103 20 12
powder 104 30 8icecream 105 70 5
pen 106 10 20icecream 107 40 5
7 rows selected.
1) List down the item whose price >50.
SQL> select * from list where price>50;
NAME CODE PRICE QUANTITY-------------- ---------- ---------- ----------icecream 105 70 5
2) List down item name and item code.
SQL> select code, name from list;
CODE NAME-------------- ----------
101 powder 102 dhal103 masala104 powder 105 icecream106 pen107 icecream
7 rows selected.
SELECT-ORDER BY:
3) List down the item price in ascending order.
SQL> select * from list order by price;
NAME CODE PRICE QUANTITY-------------- ---------- ---------- ----------
pen 106 10 20masala 103 20 12
-
8/8/2019 Study of SQL Queries
16/29
powder 104 30 8icecream 107 40 5dhal 102 45 10
powder 101 50 5icecream 105 70 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 5dhal 102 45 10icecream 107 40 5
powder 104 30 8masala 103 20 12
pen 106 10 20
7 rows selected.
SELECT-GROUP BY:
SQL> select name, sum(price) from list group by name;
NAME SUM(PRICE)-------------- ----------dhal 45icecream 110masala 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.
-
8/8/2019 Study of SQL Queries
17/29
SQL> select * from list where price between 20 and 50;
NAME CODE PRICE QUANTITY-------------- ---------- ---------- ----------
powder 101 50 5
dhal 102 45 10masala 103 20 12
powder 104 30 8icecream 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 CODE PRICE QUANTITY-------------- ---------- ---------- ----------icecream 105 70 5
pen 106 10 20
IN AND NOT IN PREDICATE:
SQL> select * from list where name in ('icecream');
NAME CODE PRICE QUANTITY-------------- ---------- ---------- ----------icecream 105 70 5
icecream 107 40 5
SQL> select * from lists where name not in ('icecream');
NAME CODE PRICE QUANTITY-------------- ---------- ---------- ----------
powder 101 50 5dhal 102 45 10masala 103 20 12
powder 104 30 8 pen 106 10 20
-
8/8/2019 Study of SQL Queries
18/29
TCL COMMANDS
TRANSACTION CONTROL LANGUAGE
The TCL commands are:CommitRollback Save point
-
8/8/2019 Study of SQL Queries
19/29
1.COMMIT:-
Syntax:set auto commit on;
set auto commit off;commit;
Description:
Commit commands tells the DBMS to make permanent changes made to temporarycopies of the data updating the permanent database tables to match the updated temporarycopies.
2.ROLL BACK:-
Syntax:rollback;
Description:
Roll back tells the DBMS to undo any changes made to the DBMS after the mostrecent commit.
3.SAVE POINT:-
Syntax: savepoint ;
Description:
Save point are like markers to divide a very lengthy transaction to smaller ones. Theyare used to identify a point in transaction to which we can later rollback. Thus savepoint isused 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-------------- ---------- ---------- ----------
-
8/8/2019 Study of SQL Queries
20/29
powder 101 50 5dhal 102 45 10masala 103 20 12
powder 104 30 8icecream 105 70 5
pen 106 10 20icecream 107 40 5
7 rows selected.
SQL> delete from list where code=104;
1 row deleted.
SQL> select * from list;
NAME CODE PRICE QUANTITY-------------- ---------- ---------- ----------
powder 101 50 5dhal 102 45 10masala 103 20 12icecream 105 70 5
pen 106 10 20icecream 107 40 5
6 rows selected.
SQL> rollback;
Rollback completed.
COMMIT:
SQL> select * from list;
NAME CODE PRICE QUANTITY-------------- ---------- ---------- ----------
powder 101 50 5dhal 102 45 10masala 103 20 12
powder 104 30 8
icecream 105 70 5 pen 106 10 20icecream 107 40 5
7 rows selected.
SQL> delete from list where code=106;
-
8/8/2019 Study of SQL Queries
21/29
-
8/8/2019 Study of SQL Queries
22/29
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 5vennila 102 45 10masala 103 20 12
powder 104 30 8chocolate 105 70 5icecream 107 40 5
6 rows selected.
SQL> rollback to s2;
Rollback complete.
SQL> select * from list;
NAME CODE PRICE QUANTITY-------------- ---------- ---------- ----------
powder 101 50 5vennila 102 45 10masala 103 20 12
powder 104 30 8icecream 105 70 5icecream 107 40 5
6 rows selected.
-
8/8/2019 Study of SQL Queries
23/29
DCL COMMANDS
DATA CONTROL LANGUAGE
The DCL commands are:Grant
Revoke
-
8/8/2019 Study of SQL Queries
24/29
1. GRANT:-
Syntax:Grant on to user;
Description:
Grand gives specifies SQL statement access or individual data objects to a user or agroup of users.
2. REVOKE:-
Syntax:Revoke on from user;
Description:
Revoke removes specific SQL statement access previously granted on individualdatabase objects from a user or group of users.
PREDICATES
SQL> select * from itemlist;
CODE ITEM PRICE------------ --------- ---------
1 Pen 20.5
-
8/8/2019 Study of SQL Queries
25/29
2 Notebook 70.753 Bat 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 ITEM PRICE------------ --------- ---------
2 Notebook 70.753 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:
-
8/8/2019 Study of SQL Queries
26/29
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_namewhere column_name=(`value`);
Example:
SQL> select * from itemlist where exists(select icode from itemlist where icode=3);
CODE ITEM PRICE------------ --------- ---------
1 Pen 20.52 Notebook 70.75
3 Bat 99.99
CONSTRAINTS
1. DOMAIN INEGRITY CONSTRAINTS:-
i. NOT NULL:
-
8/8/2019 Study of SQL Queries
27/29
Syntax: Create table(field datatype)constraintnot null);
SQL> create table details(no number(3),name varchar(10),salary numeric(4,2) constraint salnot null);
Table created.
SQL> desc details;
Name Null? Type------------- ------------- -------------NO NUMBER(3)NAME VARCHAR2(10)SALARY NOT NULL 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? Type------------------ -------- --------------
EMPID NUMBER(3)EMPNAME VARCHAR2(10)SALARY 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 primarykey(id,name));
-
8/8/2019 Study of SQL Queries
28/29
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:4Error 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;
-
8/8/2019 Study of SQL Queries
29/29
Name Null? Type----------- ------------ -----------ID NUMBER(5)
RESULT:-
Thus the various SQL QUERIES were studied and executed successfully.