Page 2 :
‘ Me, piculsction whey Columns + vel, , —1 Display the salary &.10% of salary along with the name from table, select Fname,salary,salary *.10, From rae ., , PS Mode S$ Nal Me j, , ~» Display all the rows where the middle name is not empty., >select emp_no, fname, From employees, Where mname is not null;, , _» Display all the rows where the middle name is empty., select emp_no, fname, From employees, Where mname is null;, , , , To create table” Department” Qoet, create table Department ; dent, (Dept_no smallint primary key, i \ LE, DeptName varchar(20), _ 2 Deg Lane, is Managerld smallint, - ), , DOJ date,, , Location varchar(20),, , Emp_no smallint,, , Foreign key(Emp_no) references employees(Emp_no));, , To insert record into the table “Department”, insert into department values, (101,’Marketing’,11,’2001-05-07’,’Mumbai’,4),, (102,’Finance’,12,’1991-08-23’,'Surat’,3),, (103,’Marketing’,13,’2006-03-03’,’Dahanv’,1),, (104,’Salse’,14,’1996-11- 13’,’Delhi’,2),, , cra pealee 15, ‘2001- 05- 07, ‘Mumbai’ iL);, , , , s earth the ésaeearie in capital, reverse leey year & day name of joining, , date from department table., select upper(deptname),reverse(location), Year(doj),dayname(do)), , From department;
Page 3 :
( TALL, where Lone % \, ey Display only row containing the location ‘Mumbai’, select * from department, Where location=’mumbai’;, , ‘, , Display Dept_no, department name & date of joining where year >2000, , Select dept_no,deptname,doj, From department, Where year(doj)>2000;, , Logical Oprator:, AND, OR NoT, , —» Display all column if emp_no =1 and location=Mumbai, select * from department, Where emp_no=1 and location=’ Mumbai’;, , Display the dept_no, Emp_no, Location columns if emp_no=1 or, location=Mumbai., select dept_no,Emp_no,location From department, Where emp_no=1 or location=’mumbai’;, , Display the Dept_no ,Department name if department is not equal, to Salse., , select Dept_no from department, , Where deptname!=’sales’:, , Between operator, s Display the Emp_no, name and salary, where the salary is 15000 or, more but 19000 or less from Employees table., select Emp_no,Fname,salary, From Employees, Where salary between 15000 and 19000;
Page 4 :
erator pe A dee ?, tee the Dept_no, name and location if location is either Mumbai or surat., , select Dept_no,Deptname, location, From department ,, Where location in(’mumbai’,’surat’);, , ca, , Locettter -' Mumbai v1 o, Like operator, “Pattern > Represents, ‘b%’ > _ starting with b or B, ‘%b’ > — ending with b or B, ‘%b%’ > containing b or B in any position, ‘__’ > contains any 3 characters, “A_'’ > __ has 3 char & the second char isa or A, ‘_c%’ > — third char is corC, ‘%c_'’ > the second last char is c or C, ‘a%c’ > first char is a or A & the last char is corC, , Display employee no, employee name salary if employee name has second, alphabet ‘i ‘., , select emp_no,fname,salary, , From employees, , Where fname like ’_i%’;, , Distinct, , Display the name of the city without any duplicates in department table., select distinct location, From department;, , Limit, , Display the 1* three rows of the table department., select * from department, Limit 3;, , Display 2™ and 3 rows of the table department. Z, select * from department, Limit 1,2;, >, Se get, , rine ett 95, grr
Page 5 :
Order by (sorting), , Display emp name and salary in the alphabetic order of name., select fname,salary, from employees, order by fname;, , Display department name and manager id in ascending order of deptname then, manager id. ‘, , >select deptname,managerld, , From department, , Order by deptname, managerld;, , Display department name and manager id in ascending order of deptname then, descending order of manager id., , select deptname,managerld, , From department, , Order by deptname,managerld desc;, , Sorting by column position, Display the emp_no, name,salary in descending order of name(column 2)., , >select emp_no,fname,salary, , From employees, Order by 2 desc;, , Where & order by, Display the table in alphabetic order of name where the salary > 15000, , >select * from employees, Where salary>15000, Order by fname;, , Aggregate Function, Count(), sum() , Avg(), Max(), Min(), , To find highest,lowest,sum &average of salary ., >Select max(salary),min(sala ry),sum(salary),avg(salary), , From employees;