SQL Query

SELECT fname,job_id,pub_id,convert(varchar,hire_date )FROM dbo.employee;

SELECT fname,job_id,pub_id,convert(varchar,hire_date,101 )FROM dbo.employee;

SELECT fname,job_id,pub_id,convert(varchar,hire_date,102 )FROM dbo.employee;

SELECT fname,job_id,pub_id,convert(varchar,hire_date ,103)FROM dbo.employee;

SELECT fname,job_id,pub_id,convert(varchar,hire_date,104 )FROM dbo.employee;

Function count

n  Return sigle value

use pubs;

-- Count Total number

select COUNT(*) As 'Total' from employee;

Use where

use pubs;

-- Count Total number

select COUNT(*)  from dbo.jobs where min_lvl='25';

 

AVG function

use pubs;

-- AVG < number

select * from  dbo.sales Where qty < (select Avg(qty) from sales );

Minimum function

use pubs;

-- min number

select MIN(qty) from  dbo.sales;

Max FUNCTION

use pubs;

-- max number

select MAX(qty) from  dbo.sales;

SUM FUNCTION

use pubs;

-- SUM number

select SUM(qty) from  dbo.sales;

 

Display details of all those students who have registered course whose title is ‘DBS’.

select *from student s join Reg r on s.sid=r.sid join course c on c.cid=r.cid where c.title ='DBS'

Display details of all those teachers whose age sal is more than the sal of Akram

select * from teacher t join Asg  a on t.tid= a.tid join course c on c.cid=a.cid where t.sal>(select sal from teacher where tid=5) and

t.age>(select age from teacher where tid=5)

Display details of all those students who have registered courses which are taught by Adnan.

 

select * from student s join Reg  r on s.sid= r.sid join course c on c.cid=r.cid join  Asg

as on c.cid= as.cid join teacher t on as.tid=t.tid where  t.FName='Adnan';

Display details of all those courses which are registered by student whose age is greater than 25.

select * from student s join  Rsg  r on s.sid= r.sid join course c on c.cid=r.cid

where s.age>25

Display details of all those courses which are taught by ali and enrolled by students whose age is greater than 23.

 

select * from course c join Asg a on c.cid= a.cid join teacher t on a.tid=t.tid  join Reg r on r.cid=c.cid join student s on

s.sid=r.sid

where t.TName='Ali' and s.age>23;

question 2

b)      SELECT * from Student, Reg where reg.sid = 1 or reg.cid = 2;

(π_* (σ_{reg.sid = 1 or reg.cid = 2} (Student × Reg)))

c)       SELECT * from Teacher inner join Asg on Teacher.tid = Asg.tid inner join Course on Course.cid= Asg.cid where course.category=’CS’ or Teacher.sal > 40000;

π_*(σ _{course.category=’CS’ or Teacher.sal > 40000} ((Teacher ⋈ _{Teacher.tid = Asg.tid} Asg) ⋈ _{Course.cid= Asg.cid} (Course)))

d) SELECT Course.cid, Course.ctitle, Student.sname from Course, Asg, Teacher, Reg, Student where Course.cid=Asg.cid and Asg.tid = Teacher.tid and Course.cid=Reg.cid and Studen.sid = Reg.sid and Student.sname=’Ali’ and Teacher.Tname = ‘Ali’;

(π _{Course.cid, Course.ctitle, Student.sname} (σ _{Course.cid=Asg.cid and Asg.tid = Teacher.tid and Course.cid=Reg.cid and Studen.sid = Reg.sid and Student.sname=’Ali’ and Teacher.Tname = ‘Ali’} ((((Course × Asg)× Teacher) × Reg )× Student)))

e) (SELECT sname from student where sage>24 and cgpa<2.9)

UNION (Select tname from teacher where sal>50000)

(π _sname (σ _{sage>24 and cgpa<2.9} (student))) ⋃ (π tname (σ _sal>50000 (teacher))

Question-4: Simplify the following relational queries.

a)       (σcity=‘ISB’ (σcgpa>3.0 (σCgpa>3.0)

Select *from student where city=’ISB’ and cgpa>3.0 and Cgpa<3.4

b)      (σtsal>‘30000’ (Πtname, tsal (Teacher))

Select tname, tsal from Teacher where tsal>30000

c)       (σtitle=‘cs’(course)) ⋈ (σ tid=‘1’(asg))

Select * from course  join asg  on course.cid=asg.cid join

1. Display full name of all employees whose city is RWP.

select fname+lname as 'full name' from Employee   where city='RWP';

2. Display details of those authors whose name is either Ali or Rida or Javed without using comparison/relational operator.

select * from  Authors where name in('Ali', 'Rida','Javed')

3. Display details of all those Employee whose salary is less than 25000 or salary greater than 30000 using between operator.

select * from  employee where salary<25000 or salary>25000;

4. Display details of all those male authors who are living in isb or lhr.

select * from  authors where (gender='Male'AND (city='isb' OR city='lhr'));

5. Display details of all those employees whose fname is neither akram nor asif nor ahmed without using in operator.

select top 3*from employee;

6. Display details of all those employees whose lname starts with a and whose fname second letter is a vowel character i.e. (a,e,i,o,u).

select * from  Employees where lname like '[aeiou]%';

7. Display details of all those authors whose name contains less than 5 characters.

select * from  Authors where LEN(name) < 5;

8. Display fname of 3 employees who are earning least.

select top 3 name  from employee where salary in((select max(salary) from employee)) ;

9. Display details of all authors in such a way that female authors are shown before the male authors.

select  *  from  authors order by gender ASC;

10. Display the unique cities from author table

Select distinct cities from author;.

11. Display details of all those employees who are getting salary greater than 50000 or whose city is ISB.

select  *  from  employee where (salary<50000) OR (city='ISB');

12. Display details of all those authors whose age is less than equal to 50 and age is greater than equal to 20 without using relational operators i.e. (>=, <=)

Select * from authors where age between 20 and 50 ;

13. Display all authors.

Select *from authors;

14. Display details of all those employees whose salary is between 20000 and 30000 without using between operator.

Select * from authors where salary between 20000 and 30000

15. Display details of all those authors whose city is either rwp or isb using in operator, in such a way that youngest author should be shown at the top.
16. select *  from author where (city=’rwp’ or city=isb) AND (salary in((select max(salary) from employee))) ;