Structured Query Language - All commands Notes

SQL NOTES

1. Structured Query
Language

2. Disadvantage of File Processing System
•Duplication
•No consistency
•Sharing was a problem
•Security

3. Database Management System
•Data –collections of raw facts
•Processed data –information
•Record –row/tuple/
•Database –collection of relevant tables
•Table –collection records and columns/fields/attribute

4. Empid Empname Designatio
n
Salary Dno
101 Justin TL 15000 10
102 Mary Manager 25000 20
Employee
table
Primary Key Child tableSecondary
table
Foreign key
Dno Deptname Location
10 Sales Canada
20 HR UK
Department table
Primary key Primary table /Parent Table

5. •Database Management System
•DBMS software-creating the database,alterthe database, inserting data in to
table, deleted the existing date, do modifications, sharing, giving security……
•n number of dbmssoftwares
•MySQL-free source

6. SQL-commands
•Data definition commands-CREATE,ALTER,RENAME,DROP,TRUCATE
•Data manipulation commands-INSERT ,UPDATE,DELETE
•Data transaction commands-COMMIT,SAVEPOINT,ROLLBACK
•Data Control commands-GRANT,REVOKE
•Data retrieval/query command-SELECT

7. Data Definition language commands
•Create –Creating the database object-table,view,index…etc
•Alter-to change the structure by adding column removing column
•Truncate-delete only records, table will exist
•Drop –delete both table and records
•Rename –to change the name of the table

8. •Commit –saving
•Rollback -undo
•Rollback will not work in three situations:
•When power is off
•DDL command
•Commit
•CUI

9. create
•Create table <table name>(
•Colnamedatatype(size),
•Colname2 datatype(size),
•.
•Cnamendatatype(size));
•Create table album(aid numeric(2), anamevarchar(20),year numberic(4),artist
varchar(20));

10. •Create table employee(
Empid number(3),
Enamevarchar(20),
Desgvarchar(15),
Dojdate,
Salary number(6));

11. Varchar
•Name varchar(10)
•Ravi
•4 byes is used left out is 6 bytes is
unused
Varchar2
•Name varchar2(10)
•Raviteja
•8 bytes are used
•Remaining 2 bytes is released

12. •Insert into <tablename> values(…………..);

13. insert
•Insert into <tablename> values(colvalue,col2,col3);

14. Constraints
•UNIQUE –ACCEPTS ONLY UNIQUE VALUES
•NOT NULL –WILL NOT ACCEPT NULL VALUES
•PRIMARY KEY –UNIQUE + NOT NULL
•FOREIGN KEY –CONNECT TWO TABLES
•CHECK –TO RESTRICT THE VALUES GETTING ENTERED

15. Alter command is used to change the structre
of the table
•Alter table <tablename> add/drop/modify

16. Alter –to change the structure of the table
•Add new column
•Drop a existing column
•Change the data type of a column
•Change the size of the data type of a column

17. •Alter table <tablename> add (mobnonumeric(10) )
•Alter table<table name> modify mobnovarchar(13)
•Alter table <table name> drop course

18. Update-to modify the content
•Update table <table name> set col=new col value where <Conditon>

19. Delete –to delete data/records from the table
•Delete from table name where condtion;

20. •Not Null
•Unique
•Primary Key
•Foreignkey
•Check

21. Eid Ename Desg deptno
101 Akash TL 20
102 Aman Manager 30
PK FK
Secondary table Child Table
Deptno Dname location
10 Finance US
20 HR Indonesia
30 Marketing India
PK Primary table Parent Table

22. •Ename, dname, deptno=20
•Join condition will be 1
•Condition 2 deptno=20
•Display the employee name, dept name whose salary is equal to 10000.
•select emp.ename,dept.dnamefrom emp, dept where emp.dno=dept.dnoand
sal=10000;

23. •Select */<list of columns> from <table name>
Where <condition>
Group by <column name>
Having <group by condition>
Order by <column name> asc/desc;

24. Operator in SQL
•Relational/Comparison operators: --
•<, >, >=, <=, =, !=
•Range operator
•Between …and →range
•Like
•_ for single character
•% for n number of characters
•IN and NOT IN

25. Group functions/ Aggregate functions
•Sum()
•Avg()
•Min()
•Max()
•Count(*)/count(colname)

26. •>
•<
•>=
•<=
•=
•!=….<>
•In
•% --n number of character
•_ single character
•Between and ---range operator
•like

27. 5.99 or 6.99 or 3.99
11.99
10.99
9.99
9.98
8.99
8.97
7.99
7.98
6.99
5.99
5.98
4.99
3.99
3.98
2.99
1.99
1.98
0.99
0.00

28. •Constraint <constrainname> primary key (<colname>)

29. Joins
•Joins are used to display data from multiple tables
•Simple/inner join/equijoin/natural join –common column will be there
•Non equijoin-no columns are common
•Self join
•Outer Join

30. Simple / equi/natural join
•One column must be common in both the tables
•2 tables will have Join condion–one
•3 tables will have 2 join conditions
•4 tables will have 3 join conditions
•N table –join condition will be N-1

31. Non equijoin
•Don’t have any column common in both the tables

32. Self join
•Table joining to itself

33. Retrieving data
•Select e.ename,e.desg,d.dname
•From emp e , dept d
•Where e.deptno=d.deptno;
•Adress, district, cityname
•Cityname, country name
•District -address, cityname-city, countryname-country

34. Creating a table from a table
•Creating table using select statement
•Create table <table name> as select * from table where <condition>;

35. View
•View is a virtual table. View is a database object.
•It does not physically exist but it is stored in oracle data dictionary
•We can execute by using select
•Create view <viewname> as select columns from table where condition;

36. •Delete –works on records
•without condition-all records will get deleted
•With condition –only specific records will get deleted
•Drop –works on the table
•Both records and structure of the table gets deleted
•Truncate –works on the table
•Only records gets deleted structure remains as it is.

37. Types of views
Simple view
•Creating view on one table
•Creating using only select statement
Complex view
•Creating the view using multiple
tables
•Created by using joins