SQL Solutions to practice problems-#1



DROP TABLE CLASS;
     CREATE TABLE CLASS
	(CL_# CHAR(4) NOT NULL,
	C_# CHAR(3),
	SECT_# CHAR(1),
	I_ID CHAR(4),
	ROOM CHAR(4),
	PRIMARY KEY (CL_#),
	FOREIGN KEY (C_#) REFERENCES COURSE,
	FOREIGN KEY (I_ID) REFERENCES INSTRUCTOR);

    INSERT INTO CLASS VALUES
	('101', '673', '1', '1', '6303');
    INSERT INTO CLASS VALUES
	('102', '673', '2', '1', '806');
    INSERT INTO CLASS VALUES
	('103', '674', '1', '3', '9002');
    INSERT INTO CLASS VALUES
	('104', '674', '2', '1', '8004');
    INSERT INTO CLASS VALUES
	('105', '340', '1', '2', '1213');

1.  
select *
from student;

S_ID LNAME      ADDRESS                   CITY
---- ---------- ------------------------- ----------
1234 James      123 Lyle Ave              Chicago
2341 Jones      34 Kemp St                Kenwood
7834 Lance      1 Taylor St               Oak Park
2375 Ames       123 Lake Ave              Chicago
956  Sly        74 May Ave                Kenwood
239  Ray        34 St                     Lansing


2.
select s_id, lname, address, city
from student
order by lname;


S_ID LNAME      ADDRESS                   CITY
---- ---------- ------------------------- ----------
2375 Ames       123 Lake Ave              Chicago
1234 James      123 Lyle Ave              Chicago
2341 Jones      34 Kemp St                Kenwood
7834 Lance      1 Taylor St               Oak Park
239  Ray        34 St                     Lansing
956  Sly        74 May Ave                Kenwood

3.
select *
from student
where city = 'Chicago';


S_ID LNAME      ADDRESS                   CITY
---- ---------- ------------------------- ----------
1234 James      123 Lyle Ave              Chicago
2375 Ames       123 Lake Ave              Chicago


4.
select lname, grade 
from student, enroll 
where student.s_id = enroll.s_id
and grade = 'A';


LNAME      GRADE
---------- -----
James      A
Lance      A


5. 
select lname, cname, grade
from enroll, course, student, class
where student.s_id = enroll.s_id
and class.CL_# = enroll.CL_#
and class.c_# = course.c_#
and grade = 'A'
and cname = 'Systems'; 


LNAME      CNAME      G
---------- ---------- -
Lance      Systems    A



6. 
select lname, cname, grade
from enroll, course, student, class
where student.s_id = enroll.s_id
and class.Cl_# = enroll.CL_#
and class.c_# = course.c_#
and grade = 'A'
and cname = 'Systems'; 

LNAME      CNAME
---------- ----------
Jones      MIS


7.
select lname, grade, cname
from enroll, course, student, class
where student.s_id = enroll.s_id
and class.cl_# = enroll.Cl_#
and class.c_# = course.c_#
and lname = 'James'
and cname = 'Systems';


LNAME      GRADE CNAME
---------- ----- ----------
James      B     Systems


8. 
select i_name,sect_#, cname
from class, instructor, course
where instructor.i_id = class.i_id
and class.c_# = course.c_#
and sect_# = '2'
and cname = 'Database'; 


I_NAME     SECT_# CNAME
---------- ------ ----------
Thomas     2      Database


9.
select room, cname
from class, course
where class.c_# = course.c_#
and cname = 'Database' 
and sect_# = '1';

ROOM CNAME
---- ----------
6303 Database


10.
select room, cname
from class, course
where class.c_# = course.c_#
and room = '8004';

CNAME      ROOM
---------- ----
Systems    8004


11. 
select grade, count(grade)
from enroll
group by grade;


G COUNT(GRADE)
- ------------
A            2
B            3
C            2
D            1


12. this solution uses an alias as the column name
select grade, count(grade) as #_of_grades
from enroll
group by grade
having count(grade) > 1;



13. How many A and B are there?
select grade, count(grade)
from enroll
where grade = 'A' OR GRADE = 'B'
group by grade;

or

select grade, count(grade)
from enroll
where grade in ('A','B')
group by grade;





GRADE COUNT(GRADE)
----- ------------
A                2
B                3