STRUCTURED QUERY LANGUAGE
1)Create command
It is used to construct an empty table
Eg:create table emp
eid varchar(30),
ename varchar(30),
depid number(30),
salary number(30),
job varchar(30));
2)Student:
create table student
sid varchar(30),
sname varchar (30),
class number(30),
marks number(30),
percentage varchar(30));
3)Patient:
create table patient
pid varchar(30),
pname varchar (30),
padress varchar(30),
pbill number (30),
emailed varchar(30));
4)Product
Create Table product
pid varchar(30),
pname varchar (30),
price varchar(30),
quality number (30),
emailed varchar(30));
D)Mobile
Create Table product
SNo number(30),
IMEINo varchar(30),
Model varchar (30),
price varchar(30),
DOM date(30));
2)Insert
It is used to inserting the values in a table
Insert into emp values(‘&eid’,’&ename’,’&depid’,’&salary’,’&job’);
Insert into student values(‘&sid’,’&sname’,’&class’,’&marks’,’&percentage’);
Insert into product values(‘&pid’,’&pname’,’&price’,’&quantity’,’&emailid’);
Insert into Mobile values(’&SNo’,’&IMEI’,’&Model’,’&Price’,’&DOM’);
Insert into patient values(‘&pid’,’&pname’,’&padress’,’&pbill’,’&emailid’);
3)Select
Employee
Select with single: Select eid from emp;
Select with Multiple: Select eid,ename,job from emp;
Select with all: Select * from student;
Product
Select with single: Select pid from product;
Select with Multiple: Select pid,pname,price from product;
Select with all: Select * from product;
Student
Select with single: Select sid from student;
Select with Multiple: Select sid,sname,class from student;
Select with all: Select * from student;
Patient
Select with single: Select pid from patient;
Select with Multiple: Select pid,pname,bill from patient;
Select with all: Select * from patient;
4)Rename
Changing the existing table name
Rename emp to emp1
Rename emp emp1
5)Alter :
It is used to change modify removing the column heading
a)Alter with add:
Eg:Alter table emp add(Doj Date);
Alter with modify:
Eg:Alter table emp modify(salary number(30));
c)Alter with rename:
Alter table emp Rename column salary to salary;
Queries :
Construct the student table Sno,rno,name,course,marks,percentage.
A) create table student
sno varchar(30),
rno varchar (30),
name number(30),
course number(30),
marks number(30),
percentage varchar(30));
2)To fulfil all the values in student
A)select* from student;
3)To change column name rno to hallticket no.
A)Alter table student Rename column rno to hallticket no,;
4)To change course datatype
A)Alter table student modify(course varchar(30));
5)To change the table name student to Great BCA-AI
A)Rename Student to Great BCA-AI
6)Update
Update with all:
Update emp set salary=salary*2;
Update with single:
Update emp set salary=salary*2 where eid=103;
Queries
1)Construct product table Sno,pid,pname,DOM,Price
A) Create Table product
SNO number(30),
pid varchar(30),
pname varchar (30),
DOM date(30),
price number(30));
2)To fill all pvalues
A) Insert into product values(‘&SNO’,’&pid’,’&pname’, ,’&DOM’, ’&price’);
3)To list sno,pname,price from product table
A) Select sno,pname,price from product;
4)To change the col.name price to product price
A) Alter table product Rename column price to producttprice;
5)To change product price 7000 to 18000 where pid is 104
A)Update Product set Product price=1800,where pid=104;
6)To change the column datatype I.E,DOM
Alter table product modify(DOM Date);
7)Delete:To delete particular row
Delete from emp where eid=105;
8)Truncate:To delete all the rows
Truncate table emp;
9)Drop:To remove the table permanently
Drop table emp;
10)Commit:It stores the tables
Commit;
Queries
1)List all product values
A) Select * from product;
2)To change the column name name into sname
A) Alter table Student Rename column name to sname;
3)To remove row where sid is 108
A)Delete from student where sid=108;
4)To change the marks 423 into 740 where sid is 108
A)Update student set marks =740 where sid=104;
5)Which command used for storing the student table
A)commit;
11)Roll back:To restore the values
Rollback;
12)Description:It is used to display the structure of the table
Desc emp;
STRUCTURED QUERY LANGUAGE
Sub languages are 5 types they are
1)DDL(Data Defnition Language):Create,Rename,Alter,Drop,Truncate,Description
2)DML(Data Modified language):Insert,Update,Delete
3)DQL(Data Query Language):Select
4)TCL(Transaction Controlling Language):Commit,Rollback
5)DCL(Data Controlling Language):Grant,Revoke
OPERATORS
1)Arithmetic operators
2)Relational operators
3) Logical operators
4) String operators
5) Set operators
6) Special operators
1)Arithmetic operators
Addition:select eid,ename,salary+20000 from emp;
Subraction: select eid,ename,salary-20000 from emp;
Multiplication: select eid,ename,salary*20000 from emp;
Division: select eid,ename,salary/20000 from emp;
2)Relational operators
1)>
Select * from emp where salary>5000;
2)<
Select * from emp where salary>5000;
3)=
Select * from emp where salary=5000;
4)<>
Select * from emp where salary<>5000;
5)>=
Select * from emp where salary>=5000;
6)<=
Select * from emp where salary<=5000;
Queries:
1)List all the student values
A)Select * from student;
2)List Sno,rno,Sname in the student table
A)Select sno,rno,sname from student;
3)List all the student values where the marks more than 500
A)Select sno,rno,sname where marks>500;
4)List sno,rno,marks in the student table where the marks increasing 10
A)Select sno,rno,marks+10 from student;
5)List all the student values where the name exactly Raju
A)select * from student where name=’Raju’;
6)List all the student values where the marks excluding 450
A)select * from student where marks<>450;
3)Logical operators:
The logical operators are and ,or,not
and:
Select * from emp where eid=101 and salary=4000;
or:
Select * from emp where eid=101 or salary=4000;
not:
Select * from emp where eid=101 not salary=4000;
1. Display all the emp values where eid is 101 and salary is 9000
A)Select * from emp where eid=101 and salary=9000,
2. Display all the emp values where eid is 105 or name is ‘Raju’
A) Select * from emp where eid=105 or ename=’Raju’,
3. Display all the emp values in dep is 20 & earnings more than 200
A) Select * from emp where depid=20 and salary>200,
4)Sting operators:
I. % : Select * from emp where ename like ‘R%’;
II. -:Select * from emp where ename like ‘----‘;
Queries
1.Display all the student values where the sname starts with s
A)Select * from student where sname like ‘s%’;
2 Display all the student values where the sname having R as 3rd charecter
A) Select * from student where sname like ‘---R%’;
3..Display all the emp values who are not earning 2600 and their name starts with s
A)Select * from emp where salary<>2600 and sname like ‘s%’;
4)Display all the emp values whose name starts with a and last but two charecters
Select * from emp ename like ‘%a—‘
II//Parallel: It is used to concatenation of combined two columns
Select eid,ename//job from emp;
5)Special operators
The special operators are like in between
Like:Select* from emp where ename like ‘----‘;
In:Select * from emp where salary in (4000,5000);
Between:It is evolution of ranging values
Select * from emp where salary between (4000 and 5000);
Queries:
1.Display all the emp values where salary in 6000,7000
Select * from emp where salary in (6000,7000);
2. Display all the emp values where the job in HR,Finance
Select * from emp where job in (HR,Finance);
3.List all the emp values whose salary in the range of 2000 & 5000 and their name starts with s.
Select * from emp where salary in (2000 and 5000) and ename like ‘s%’;
List all the emp valies in dep 10,20 whose salary is not in range 1000 to 2000
Select * from emp where dep in (10,20) and salary not in between (1000,2000);
Display all the employees in dep20 & earning more than 2000 and salary in range 6000,8000
Select * from emp wher dep id=20 and salary>2000 and salary and salary between (6000,8000);
6)Set operators
Union:combaining all the records –common records
Select * from emp1 union select * from emp2;
Union all:Select * from emp1 union all select * from emp2;
Intersection: Select * from emp1 Intersection select * from emp2;
Minus: Select * from emp1 minus select * from emp2;
IIIGroup functions or aggregate function /Statistical functions
i )maximum-max():select max(salary) from emp ;
ii)minimum-min():select min(salary) from emp ;
iii)sum-sum():select sum(salary) from emp ;
iv)standard deviation-std():select stddev(salary) from emp ;
v)average-avg():select avg(salary) from emp ;
vi)count-count():select count(salary) from emp ;
IV)Classes
There are three types of classes
1)order by
2)group by
3)having
1)Order by:
It is having two types
Asecending
Descending
i)Asending(Asc):select * from emp order by salary Asc;
ii)Descending(Desc): select * from emp order by salary Desc;
Queries
1)Display dep wise avg salary
Select dep , avg (salary) from emp group by depid;
2)Display job wise total salary
Select job, sum (salary) from emp group by depid;
3)Display job wise total salary for the employees working in dep 10,20
Select job, sum(salary) from emp where depid in (10,20)
And salary between (1000,4000);
4)Display class wise max of marks from and Theri name starts with s.
Select class, max(marks) from student group by class and sname starts with s%;
3)Having:Which consists of two or more conditions
Select depid,max(salary) from emp group by depid having avg(salary)>5000;
V)Functions:
Functions are of 4 types
1)Group functions
2)Trignometry functions
3)Mathematical functions
4)String
i)Group functions:
i )maximum-max():select max(salary) from emp ;
ii)minimum-min():select min(salary) from emp ;
iii)sum-sum():select sum(salary) from emp ;
iv)standard deviation-std():select stddev(salary) from emp ;
v)average-avg():select avg(salary) from emp ;
vi)count-count():select count(salary) from emp ;
ii)Trignometric functions:
sin:Select sin(30) from dual;
cos: Select cos(45) from dual;
tan: Select tan(90) from dual;
3)Mathematiccal functions:
i)power:Select power(2,4)from dual;
ii)sqrt: Select sqrt(16)from dual;
iii)mod: Select mod(3,2)from dual;
iv)Round: Select Round(27.8888888888889,4)from dual;
v)Absolute:(Abs): Select power(2,4)from dual;
4)String functions
i)Upper()
select eid,upper(ename), salary fom emp;
ii)Lower()
select eid,lower(ename),salary from emp;
iii)Length()
select eid,length(ename),salary from emp;
iv)LTrim()
select eid,LTrim (hi,5,2),salary from dual;
v)RTrim()
select eid, RTrim (Hai,6,5,4,6),salary from dual;
Dual: is a repository it provides all the calculation values auch as trigonometry values ,mathematical
values,Date function values
Query
List all the manager in dep 20,30 & earnings more than 25000
Select * from emp where depid in (20,30) and job=’manager’ and salary>25000;
Or
Select * from emp where depid=20 and job=’manager’ and salary>25000 or depid=30 and
job=’manager’ and salary>25000 ;
8)Constrains:is a condition a business rule which can be applied for database table
Constrains are 3 types
1)Domain integrity
2)Entity integrity
3)Referential integrity
1)Domain integrity constraint
i)Not null: it shouldn’t accept zero values & blank spaces but it accept duplicate values.
create table emp
eid varchar(30),
ename varchar(30) Not Null,
depid number(30),
salary number(30),
job varchar(30) Not Null);
ii)check:evaluation of ranged values
create table emp
(
eid varchar(30),
ename varchar(30) Not Null,
depid number(30),
salary number(30) Check (Salary>6000),
job varchar(30) Not Null);
2)Entity Integrity Constraint:
i)Unique:It shouldn’t accept zero values ,blank spaces duplication valies presence at one
valuepresence at only one tiome iin the table
create table emp
(
eid varchar(30) Unique,
ename varchar(30) Not Null,
depid number(30),
salary number(30) Check (Salary>6000),
job varchar(30) Not Null);
Referential integrity constraint:
It contains only one key:
Foreignkey:The one key creates two or more tables .The 1st table is called parent table and the other
remaining tables Are called Child table.
////
Primary: *It doesn’t come under Referential integrity key.*
Primary key defines row uniquely from table
It shouldn’t accept zero valies ,blankspaces & duplicate values i.e, one value present only one time in the
table
create table emp
(
eid varchar(30)
primary key,
ename varchar(30) Not Null,
depid number(30),
salary number(30) Check (Salary>6000),
job varchar(30) Not Null);
Interview questions :
1)Difference between alter & Update
2)What is primary key
3)What is Foreign Key
4)Difference between drop,truncate & delete
5)What isPurpose of rollback
6) What are classes in SQL.
7)What are Normalisation
8)What are the grouping functions
9)Difference between % and –
10)What are the data types in sql(Char,Varchar,Number,Date,Clob)
Clob:It is used for storing Multimedia values
11)What are operators in SQL
12)Sublanguages
