1 / 53

Structured Query Language

Structured Query Language. What is a database? It’s a collection of data What is DBMS? It’s a software to manage the data What is RDBMS?

essien
Download Presentation

Structured Query Language

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Structured Query Language

  2. What is a database? It’s a collection of data • What is DBMS? It’s a software to manage the data • What is RDBMS? It’s a DBMS to represent the relational model. Data is stored in tables. Tables have columns, rows and names. Tables can be related to each other if each has a column with a common type of information. Eg. Oracle, Microsoft SQL Server, MySQL

  3. The relational model was first put forth by Edgar F Codd • In 1970, A company called Relational Software Inc, now known as Oracle, released a product called SQL to query the data stored in the oracle DBMS • SQL * Plus is a tool for writing SQL queries

  4. SQL * Plus is not case sensitive to SQL commands but it matters only when alphanumeric values are given within single quotes. • SQL is basically divided into 4 categories of languages: a) DDL (Data Definition Language) Create, alter, drop, truncate, comment b) DML (Data Manipulation Language) Insert, Update, delete, call, Explain plan, Lock Table

  5. c) DCL (Data Control Language) Commit, Savepoint, rollback, Set transaction, Grant, Revoke d) DQL (Data Query Language) Select

  6. DDL

  7. Describe tablename Gives the structure of the table SQL> desc empl_rem1 Name Null? Type -------------- -------- ---- ENO NOT NULL NUMBER(5) ENAME VARCHAR2(6)

  8. SQL> create table empl_rem(eno number(5),ename varchar(6),constraint pr_rem primary key(eno)); SQL> create table empl_rem1(eno number(5) constraint pr_rem1 primary key,ename varchar(6)); SQL> create table dept_rem(depno number(5) constraint pr_rem2 primary key,eno number(5) constraint fr_key references empl_rem); SQL> create table dept_rem1(depno number(5) constraint pr_rem4 primary key,eno number(5), constraint fr_key1 foreign key(eno) references empl_rem1);

  9. SQL> create table emp_rem(eno number(4) not null, ename varchar2(5)); It prevents data from being entered into the table without certain columns having data in them. SQL> create table emp_rem1(eno number(4) unique,ename varchar2(5) unique); A foreign key is also known as a referential Integrity constraint

  10. On delete cascade SQL> create table dept_rem1(depno number(5) constraint pr_rem4 primary key,eno number(5), constraint fr_key1 foreign key(eno) references empl_rem1 on delete cascade); • Drop – drops the table and its structure • Truncate – remove all the rows in the table • Alter table tablename add(columnname datatype); • Alter table tablename modify(columnname new datatype);

  11. Alter table tablename drop column columnname; • Alter table tablename drop (columnname1, columnname2); • Alter table tablename drop primary key; • Alter table tablename drop constraint fr_rem1; • SQL> create table emp123 as select eno,ename from empl_rem1; SQL> select * from emp123; ENO ENAME --------- ------ 1 a 2 b

  12. Alter table empl_rem1 add primary key(eno); • Alter table dept_rem1 add primary key(depno,deploc); • Alter table empl_rem1 add foreign key(depno) references dept_rem1; • Alter table add(eno number(4) references dept_rem1); • Alter table empl_rem1 rename column eno to enumber;

  13. create table emp12345 as select eno,ename from empl_rem1 where 1=2; • SQL> create table emp1234 nologging as select eno,ename from empl_rem1; It creates a table without generating redo log entries.

  14. Create table empl(eno number(4),ename varchar2(10) check(ename=upper(ename)), age number(2) check(age between 20 and 30)); • Table level check is also possible. • Create table empl(eno number(3),ename varchar2(15),sal number(10,2) default 20,000); Oracle sets default constraints starting at SYS_Cn where n is a numeric value which is unique.

  15. Oracle stores constraint information in a table called user_constraints • Select owner,constraint_name,constraint_type from user_constraints where TABLE_NAME=‘EMPL_REM1’;

  16. DML and DCL

  17. Insert into emp values(1,’remya’,’27-feb-06’); • Insert into emp(eno,ename) values(1,’remya’); • Insert into emp select eno,ename from employee;

  18. SQL> insert into empl_rem values(3,'l'); 1 row created. SQL> rollback; Rollback complete. SQL> select * from empl_rem; no rows selected SQL> insert into empl_rem values(3,'l'); 1 row created. SQL> commit; Commit complete. SQL> rollback; Rollback complete. SQL> select * from empl_rem; ENO ENAME --------- ------ 3 l

  19. Implicit Commit quit, Exit, any DDL Command Delete delete from tablename; delete from tablename where columnname = ‘ABC’; Now before commit, if you rollback, recovery of records is possible. But using truncate command, it is not possible to recover the deleted records.

  20. Update: SQL> update empl_rem1 set ename =''; SQL> update empl_rem1 set ename ='b' where eno =2; SQL> update empl set salary = salary + 100 where eno = 1; SQL> update empl set salary = salary + 100; SQL> update empl_rem1 set ename = null where eno =2;

  21. ENO ENAME • ------- ------ • 1 a • 2

  22. DQL

  23. Select distinct age from employee; • SQL> select * from empl_rem1; • ENO ENAME • --------- ------ • 1 a • 2 sales • SQL> select * from empl_rem1 order by ENAME desc; • ENO ENAME • --------- ------ • 2 sales • 1 a

  24. SQL> select * from empl_rem1; ENO ENAME --------- ------ 1 a 2 SQL> select eno from empl_rem1 where ename is null; ENO --------- 2

  25. Select * from empl_rem1 where ename like ‘m_%e%’; • Select dno from dept where dname in (‘sales’,’purchase’); • Select dno from dept where dname not in (‘sales’,’purchase’); • Select dno from dept where salary between 1000 and 2000; • Select dno from dept where salary not between 1000 and 2000;

  26. SQL> Select ename from emp where age > 20 and salary >1000 or bpay<6000; AND is stronger than OR. It bind the logical expressions on either side of it more strongly than OR does. SQL> Select ename from emp where age > 20 and (salary >1000 or bpay<6000);

  27. SQL> select * from empl_rem1; • ENO ENAME • --------- ------ • 1 a • 2 b • SQL> select * from dept_rem1; • DEPNO ENO DNAME • --------- --------- ------------- • 1 1 sales • 2 1 sales • 3 2 purc

  28. SQL> select * from dept_rem1,empl_Rem1; DEPNO ENO DNAME ENO ENAME --------- --------- ------------- --------- ------ 1 1 sales 1 a 2 1 sales 1 a 3 2 purc 1 a 1 1 sales 2 b 2 1 sales 2 b 3 2 purc 2 b

  29. SQL> select empl_rem1.eno,ename,depno,dname from empl_rem1,dept_rem1 • where empl_rem1.eno = dept_rem1.eno; ENO ENAME DEPNO DNAME ------ ------ --------- ------------- 1 a 1 sales 1 a 2 sales 2 b 3 purc

  30. SQL> select * from dept_rem1; DEPNO ENO DNAME SAL • --------- --------- ------------- --------- • 1 1 sales 1200.5 • 2 1 accs 5000 • 3 2 purc 5000 • 4 1 admin 3000 SQL> select ENO,sum(sal) from dept_rem1 group by eno; DEPNO ENO DNAME SAL • --------- --------- ------------- --------- • 1 1 sales 1200.5 • 2 1 accs 5000 • 4 1 admin 3000 3 2 purc 5000

  31. SQL> select ENO,sum(sal) from dept_rem1 group by eno having sum(sal)>1200 order by eno desc; ENO SUM(SAL) --------- --------- 2 5000 1 9200.5

  32. Usage of Aliases SQL> select ENO,sum(sal) total from dept_rem1 group by eno having sum(sal)>1200 order by sum(sal) desc; ENO TOTAL --------- --------- 1 9200.5 2 5000

  33. String Functions SQL> Select upper(dname) from dept_rem1; SQL> Select lower(dname) from dept_rem1; SQL> select depno||dname from dept_rem1; DEPNO||DNAME -------------- 1sales 2accs 3purc 4admin

  34. Select ‘no’||depno from dept_rem1; • Select concat(eno,ename) from empl_rem1; • Select rpad(eno,20,’.’),ename from empl_rem1; • Select lpad(eno,20,’.’),ename from empl_rem1; • Select rtrim(ename,’e’) from empl; • Select ltrim(rtrim(ename,’e’),’c’) from empl; • Select initcap(ename) from empl; • Select ename,length(ename) from empl;

  35. Select substr(ename,4,3) from empl; • Select substr(ename,-4) from empl; • SQL> select * from empl_rem1; • ENO ENAME --------- ------ • 1 meena • Select instr(ename,’ee’,1,1)+2 from empl; • INSTR(ENAME,'EE',1,1)+2 • ----------------------- • 4

  36. Select eno,ename from empl_Rem1 order by length(ename); • SQL> select ename from empl_rem1 where soundex(ename)= soundex('men'); • ENAME • ------ • Meena

  37. Number functions

  38. Select bpay+hra as total from empl_rem1; SQL> select * from dept_rem1; DEPNO ENO DNAME SAL --------- --------- ------------- --------- 1 1 sales 1200.5 2 1 accs 5000 3 2 purc 4 1 admin 3000 SQL> select nvl(sal,2000) from dept_rem1;

  39. Max, min • Least(bpay,total) • greatest(bpay,total) • Count(distinct ename) • Count(all name) • Count(*) = counts all rows of the table irrespective of null values.

  40. ABS(146)=146 ABS(-30)=30 Ceil(2)=2 Ceil(1.3)=2 Ceil(-1.3)=1 Floor(2)=2 Floor(1.3)=1 Floor(-1.3)=2 Mod(10,3)=1 Power(3,2)=9 Sqrt(64)=8 Exp(3)=20.08 Ln(3)-1.098 Log(10,100)=2 Round(66.666,2)= 66.67 Trunc(66.666,2)= 66.66 Sign(-3)=-1 Trignometric functions Stddev,variance AVG(sal) Sum(sal)

  41. Dates

  42. Dual table = single column one row table • Sysdate • Add_months(sysdate,6) • Add_months(sysdate,-6) • Greatest(sysdate,holidaydate) • Greatest(to_date(’20-jan-02’),to_date(’30-feb-04’)) • Least • Next_day(cycledate,’Friday’) • Last_day – last day of the month last_day(cycledate)

  43. Months_between(sysdate,bdate) • To calculate age, Months_between(sysdate,bdate)/12 • SQL> select to_char(sysdate,'mm/dd/yy') from dual; • TO_CHAR( • -------- • 02/27/06 SQL> alter session set nls_date_format='dd/mm/yy'; Session altered. SQL> select sysdate from dual; SYSDATE -------- 27/02/06

  44. Select to_date(’02/22/05’,’mm/dd/yy’) from dual; • To_char(to_date(’22-feb-06’),’day’) • When you insert, if century values are not given, oracle database will default take it as the current century.

  45. Union, Intersection and minus • Select name from temp_empl union select name from per_empl; • Select name from dayscholars intersect select name from mscstuds; • Select name from mscstuds minus select name from dayscholars;

  46. Decode Select ename,decode(sal,’1000’,’peon’,’2000’,’clerk’,’manager’) desig from empl;

  47. JOINS

  48. Joins are used to select data from two or more tables Types of joins: • Equi Join • Outer Join (Left, Right, Full) • Cross Join • Self Join

  49. Customers: • Customer_ID Name 01 Jasmine 02 Remya 03 Smitha 04 Geetha Orders: Prod_ID Product Customer_ID 234 Printer 01 657 Table 03 865 Chair 03 875 Scanner

  50. Equi Joins • Find the names of the customers and the products they ordered. Select customer.name, order.product from customer, order where customer.customer_id = order.customer_id; Select c.name, o.product from customer c, order o where c.customer_id = o.customer_id;

More Related