I). INTRODUCTION Database: The set of interrelated data is known as a database. DataBase Management System: The software that manages the data once the interrelationships are specified is known as a database management system or DBMS. RDMS: Relational Data Base Management System. Relational data base consists of relations. A relation or relational table is a two dimensional table with special properties. A relational table consists of a set of named columns and an arbitrary number of rows. Columns are also called attributes (fields) and rows are often called tuples. Each column is associated with a domain. SQL: SQL or Structured Query Language (pronounced as “sequel”) has emerged as the standard database language. SQL is now available with most RDBMS products as the database language. Components of SQL: SQL operates on a database. Some SQL commands are used to define the database objects, others to manipulate them. A database is a collection of structures with appropriate authorizations and accesses defined. The table, views (which are logical ways of looking at tables) and indexed are structures in the database and are called the objects of the database; their names and those of the columns in table and views are the identifiers. SQL commands are instructions given for some operation on the database. An SQL command has one or more distinct parts called clauses. Each clause has keywords and arguments. A keyword is a word that has a special meaning in SQL. Keywords are instructions and may not be used as identifiers. Arguments are used to complete the clause, and may have identifiers, connectors, operators and values. The components of SQL are in three categories. 1. Data definition language: DDL, which is used for specifying the database scheme. It is used to create, modify and destroy tables, views and indexes. 2. Data manipulation language: DML, to manipulate data such as to insert, modify, delete or query the data in the database. 3. Data control language: DCL to grant and revoke authorization for database access, auditing the database use and dealing with transactions. 1 II). SQL queries A) DDL commands: Data Definition Language 1).create 2).alter 3).drop 4).truncate 1).create: To create a table syntax: SQL>create table
(field_name1 datatype, field_name2 datatype, field_name3 datatype); EX1: create table students (name varchar2(20), rollnumber number, marks number); EX2: create table emplyee ename varchar2(20), eno number, esalary number, edob date); 2. alter: To alter the table . Syntax: i) adding column SQL> alter table students add （sdob date）; ii) modify sql> alter table students modify (sdob varchar2(20)); 2 3. DROP: - To delete the entire table syntax: SQL> drop table EX: SQL> drop table employee; 4. TRUNCATE:- to delete all the data in the table syntax: SQL>truncate EX: SQL>truncate students; 5. DESC:- to describe the table information syntax: SQL> desc EX: SQL> desc students; ----------------------------------------------------------------- ---------------------------------------------------------------------------------------------- B). DML (Data Manipulation Language): commands: 1. insert 2. select 3. update 4. delete 6). INSERT: to insert values into table syntax: SQL> insert into values (value1,value2,value2.....valuen ); EX SQL> insert into students values (001,'ram','bca','pgcollege'); SQL> insert into students values (002,'raj','mca','pgcollege'); 3 NOTE: To insert multiple values into the table. Syntax: SQL>insert into values ('&fieldname',&fieldname,....); EX: SQL> insert into students values ('&name',&rollnumber,&marks); NOTE: To insert more values just give a slash(/) and press enter. 7). SELECT: To retrieve the values from table syntax: i)SQL> select * from tablename; ii)SQL> select *from tablename where fieldid=value; EX: i). SQL> select * from students; ii). SQL> select *from students where sid=001; 8). UPDATE: To update the data of the table syntax: SQL> update set = where =; EX: SQL> update students set name='ali' where sid=001; 9). DELETE : To delete details of table's specified data syntax: SQL> delete from where =; EX: SQL> delete from students where sid=2; 4 C). DCL (Data Control Language): 10). COMMIT : to save all changes in database syntax: SQL> commit; EX; SQL> commit; 11). TRUNCATE: To remove whole the data from table syntax: SQL> truncate table ; EX: SQL> truncate table students; 5 III). INTIGRITY CONSTRAINTS 12).UNIQUE: To do not allow duplicate values example: SQL> create table students100 (id number unique, name varchar2(20), marks number); --------------------------------------------------------------------------------------------------------------------------------------------------------------- 13). NOT NULL: To do not make a field empty example: SQL> create table students100 (id number not null, name varchar2(20) not null, marks number); 9. PRIMARY KEY: to make the field unique and not null example: SQL> create table students100 (id number primary key, name varchar2(20), course varchar2(10), address varchar2(20), mobile number); 10).foreign key: -to make refernce of the primarykey to another table syntax: sql> create table ( field1 datatype, field2 datatype, field3 datatype, foreign key(field1) references ); ex: sql> create table marks( id number, sub1 number, sub2 number, sub3 number, 6 sub4 number, foreign key(id) references students100); RULES: 1). without inserting values into mastertable is not possible to insert into derived table. 2). without deleteling values from the derived table is not possible to delete from the mastertable. Q: Insert values into the students table SQL> insert into students100 values (001,'abdul','bsc','Secbad’,9087998765); insert into students100 values (002,'mahamed','bcom','tarnaka',8798768760); insert into students100 values (003,’abul’,'bca','begumpet',78765678687); insert into students100 values (004,'raghu','bca','koti',9878675654); insert into students100 values (005,'rahim','bca','paradise',786755); insert into students100 values (006,'vij','ma','koti',90786756); insert into students100 values (007,'rahman','mca','koti',9086756); insert into students100 values (008,'ahmed','mba','nacharam',90676756); insert into students100 values (009,'ramdev','mba','ramkoti',90786777); insert into students100 values (010,'raj','msc','koti',90786666); Q: insert values into marks table? insert into marks values 7 (001,80,55,66,44); insert into marks values (002,66,70,80,40); insert into marks values (003,90,,78,67); insert into marks values (004,56,67,87,45); insert into marks values (005,35,67,56,45); insert into marks values (006,,78,67,56); insert into marks values (007,67,,90,57); insert into marks values (008,69,,80,57); insert into marks values (009,67,79,70,57); insert into marks values (010,67,,90,57); 8 IV). SUBQUERIES SUBQUERY:- sub query is query within the query 1Q). Get the details of the student whose id is 2? sql> select *from students100 where id=2; ID NAME COURSE ADDRESS MOBILE ------- -------------------- ---------- -------------------- ---------- 2 mahamed bcom tarnaka 8798768760 2Q). Get the marks of student whose name is mahmed? sql> select * from marks where id=(select id from students100 where name='mahamed’); ID SUB1 SUB2 SUB3 SUB4 name -------- ---------- ---------- ---------- ---------- 2 66 70 80 40 mahamed 3Q). Get the name of students who got sub2 marks are 55? sql> select name from students100 where id=(select id from marks where sub2=55); NAME --------- abdul 4Q). Get the marks of student whose name start with r? Sql> select * from marks where id in (select id from students100 where name like ‘r%’); ID SUB1 SUB2 SUB3 SUB4 ------- ---------- ---------- ---------- ---------- 4 56 67 87 45 5 35 67 56 45 7 67 90 57 9 67 79 70 57 10 67 90 57 9 5Q). Get the marks of students who are studying mca? Sql> select *from marks where id =(select id from students100 where course =’mca’); ID SUB1 SUB2 SUB3 SUB4 --------- ---------- ---------- ---------- ---------- 7 67 90 57 6Q). Get the details of students whose name start with M and with D;? Sql> select * from students100 where name like ‘m%d’; ID NAME COURSE ADDRESS MOBILE ------- -------------------- ---------- -------------------- ---------- 2 mahamed bcom tarnaka 8798768760 7Q). Get the details of students whose sub2 marks are below 40? Sql> select * from students100 where id=(select id from marks where sub2<40); ID NAME COURSE ADDRESS MOBILE -------- -------------------- ---------- -------------------- ---------- 1 abdul bsc Secbad 9087998765 8Q). find the total marks of rahman? Sql> select sub1+sub2+sub3+sub4 from students100.marks where id=(select id from students100 where name=’rahman’); SUB1+SUB2+SUB3+SUB4 ------------------- 303 9Q). Get the marks of students whose name end with ‘V’? Sql> select * from marks where id = (select id from students100 where name like ‘%v’); ID SUB1 SUB2 SUB3 SUB4 ----- ---------- ---------- ---------- ---------- 9 67 79 70 57 10 10). Get names of students who got sub2 marks above 60? Sql> select name from students100 where id in (select id from marks where sub1>60); NAME --------- abdul mahamed abul vij rahman ahmed ramdev raj V).AGGRIGATE FUNCTIONS 11. COUNT() syntax: sql> select count(*) from ; ex: sql> select count(*) from students100; COUNT(*) ---------- 10 12. SUM() syntax: sql> select sum(attributename) from ; ex: sql> select sum(sub2) from marks; SUM(SUB2) ---------- 772 13. AVG() syntax: sql> select avg(attributename) from ; ex: sql> select avg(sub3) from marks; AVG(SUB3) ---------- 11 76.4 14. MAX() syntax: sql> select max(attributename) from ; ex: sql> select max(sub4) from marks; MAX(SUB4) ---------- 67 15. MIN() syntax: sql> select min(attributename) from ; ex: sql> select min(sub3) from marks; MIN(SUB3) ---------- 56 16 \"AND\" operator syntax: sql> select from where condition1 and condition2; ex: sql> select name from students100 where id=2 and course=’bcom’; NAME ---------- mahamed 17. \"OR\" Operator syntax: sql> select from where condition1 or condition2; ex: sql> select name from students100 where id=2 or id=3; NAME ---------- abul mahamed 12 18. \"IN\" operator syntax: sql> select from where in (); ex: sql> select name from students100 where id in (1,2,3); NAME --------- abul mahamed abdul 19. \"BETWEEN\" operator syntax: sql> select from where between and ; ex: sql> select name from students100 where id between 1 and 4; NAME ------- abdul mahamed abul raghu 20).” UNION” Operation: Syntax: Sql>( query1) union (query2); Ex: Sql> select id from students100 Union Select sub2 from marks; 13 21). “INTERSECT” operation Syntax: Sql> (query1) intersect (query2); Ex: Sql> select sub1 from marks Intersect Select sub2 from marks; SUB1 ---- 67 22). “MINUS” operation Syntax: Sql> (query1) MINUS (query2); Ex: Sql> select sub1 from marks Minus Select sub3 from marks; SUB1 ------ 35 69 VI).JOINS 23). INNER JOIN: Sql>select students100.name,marks.sub2 from students100 inner join marks on students100.id>7; NAME SUB2 -------------------- ---------- ahmed 55 14 ramdev 55 raj 55 ahmed 70 ramdev 70 raj 70 ahmed ramdev raj ahmed 67 ramdev 67 24).LEFT JOIN select students100.name,marks.sub2 from students100 left join marks on students100.id>7; NAME SUB2 -------------------- ---------- abdul mahamed abul raghu rahim vij rahman ahmed 55 ahmed 70 ahmed ahmed 67 15 ahmed 67 ahmed 78 ahmed ahmed ahmed 79 ahmed ramdev 55 ramdev 70 ramdev ramdev 67 ramdev 67 25). RIGHT JOIN sql>select students100.name,marks.sub2 from students100 right outer join marks on students100.id>7; 26). FULL JOIN select students100.name,marks.sub2 from students100 right full outer join marks on students100.id>7; VII). VIEWS AND INDEX 26). Creation of view. i). SQL>CREATE VIEW STUDENT20 AS SELECT ID,NAME FROM STUDENTS100; View created. ii). select *from student20; ID NAME ------ ----------------- 1 abdul 2 mahamed 16 3 abul 4 raghu 5 rahim 6 vij 7 rahman 8 ahmed 9 ramdev 10 raj 27). Creation of index Sql> Create index my_index on marks(id); Index is created. VIII). SEQUENCES 28). Creation of sequence SQL> create sequence stu_seq increment by 1 start with 1 minvalue 1 maxvalue 999 cycle; sequence is created. SQL>insert into students100 values (011,'abdulla','msc','Secbad’,9087998765,stu_seq.nextval); 1row inserted. 17 18
