Training World: SQL

Before discussing about SQL(structured query language) ,its better to learn for what we are using SQL? How we are using SQL? and Why we are using SQL?
The simple answer for all above question is to maintain information in database.Now you will ask what is database is?
database is nothing but the storage of large amount of data in millions of millions giga bytes of data is stored in one place is called database system.Most probably you heard about the DBMS(data base management system).

FIG 1: DATA BASE

yes this is database where all the information stored using a server where we are writing a code that may be MY SQL,PL/SQL etc.

let start MY SQL ,its nothing but third level programming language for writing code in storing the data, manipulating on data and retrieve the data from the data base system.There are few command to use SQL code.the command is as follow given below...............

let we see one example to maintain library database.

fig: library data base maintaing system

code for above :

Create table LMS_MEMBERS

(
MEMBER_ID Varchar(10),
MEMBER_NAME Varchar(30) NOT NULL,
CITY Varchar(20),
DATE_REGISTER Date NOT NULL,
DATE_EXPIRE Date ,
MEMBERSHIP_STATUS Varchar(15)NOT NULL,
Constraint LMS_cts1 PRIMARY KEY(MEMBER_ID)
);

Create table LMS_SUPPLIERS_DETAILS
(
SUPPLIER_ID Varchar(3),
SUPPLIER_NAME Varchar(30) NOT NULL,
ADDRESS Varchar(50),
CONTACT bigint(10) NOT NULL,
EMAIL Varchar(15) NOT NULL,
Constraint LMS_cts2 PRIMARY KEY(SUPPLIER_ID)
);

Create table LMS_FINE_DETAILS
(
FINE_RANGE Varchar(3),
FINE_AMOUNT decimal(10,2) NOT NULL,
Constraint LMS_cts3 PRIMARY KEY(FINE_RANGE)
);

Create table LMS_BOOK_DETAILS
(
BOOK_CODE Varchar(10),
BOOK_TITLE Varchar(50) NOT NULL,
CATEGORY Varchar(15) NOT NULL,
AUTHOR Varchar(30) NOT NULL,
PUBLICATION Varchar(30),
PUBLISH_DATE Date,
BOOK_EDITION int(2),
PRICE decimal(8,2) NOT NULL,
RACK_NUM Varchar(3),
DATE_ARRIVAL Date NOT NULL,
SUPPLIER_ID Varchar(3) NOT NULL,
Constraint LMS_cts4 PRIMARY KEY(BOOK_CODE),
Constraint LMS_cts41 FOREIGN KEY(SUPPLIER_ID) References LMS_SUPPLIERS_DETAILS(SUPPLIER_ID)
);

Create table LMS_BOOK_ISSUE
(
BOOK_ISSUE_NO int,
MEMBER_ID Varchar(10) NOT NULL,
BOOK_CODE Varchar(10) NOT NULL,
DATE_ISSUE Date NOT NULL,
DATE_RETURN Date NOT NULL,
        DATE_RETURNED Date NULL,
FINE_RANGE Varchar(3),
Constraint LMS_cts5 PRIMARY KEY(BOOK_ISSUE_NO),
        Constraint LMS_Mem FOREIGN KEY(MEMBER_ID) References LMS_MEMBERS(MEMBER_ID),
        Constraint LMS_BookDetail FOREIGN KEY(BOOK_CODE) References LMS_BOOK_DETAILS(BOOK_CODE),
Constraint LMS_FineDetail FOREIGN KEY(FINE_RANGE) References LMS_FINE_DETAILS(FINE_RANGE)
);

Insert into LMS_MEMBERS
Values('LM001', 'AMIT', 'CHENNAI', '2012-02-12', '2013-02-11','Temporary');
Insert into LMS_MEMBERS
Values('LM002', 'ABDHUL', 'DELHI', '2012-04-10', '2013-04-09','Temporary');
Insert into LMS_MEMBERS
Values('LM003', 'GAYAN', 'CHENNAI', '2012-05-13','2013-05-12', 'Permanent');
Insert into LMS_MEMBERS
Values('LM004', 'RADHA', 'CHENNAI', '2012-04-22', '2013-04-21', 'Temporary');
Insert into LMS_MEMBERS
Values('LM005', 'GURU', 'BANGALORE', '2012-03-30', '2013-05-16','Temporary');
Insert into LMS_MEMBERS
Values('LM006', 'MOHAN', 'CHENNAI', '2012-04-12', '2013-05-16','Temporary');

Insert into LMS_SUPPLIERS_DETAILS
Values ('S01','SINGAPORE SHOPPEE', 'CHENNAI', 9894123555,'sing@gmail.com');
Insert into LMS_SUPPLIERS_DETAILS
Values ('S02','JK Stores', 'MUMBAI', 9940123450 ,'jks@yahoo.com');
Insert into LMS_SUPPLIERS_DETAILS
Values ('S03','ROSE BOOK STORE', 'TRIVANDRUM', 9444411222,'rose@gmail.com');
Insert into LMS_SUPPLIERS_DETAILS
Values ('S04','KAVARI STORE', 'DELHI', 8630001452,'kavi@redif.com');
Insert into LMS_SUPPLIERS_DETAILS
Values ('S05','EINSTEN BOOK GALLARY', 'US', 9542000001,'eingal@aol.com');
Insert into LMS_SUPPLIERS_DETAILS
Values ('S06','AKBAR STORE', 'MUMBAI',7855623100 ,'akbakst@aol.com');

Insert into LMS_FINE_DETAILS Values('R0', 0);
Insert into LMS_FINE_DETAILS Values('R1', 20);
insert into LMS_FINE_DETAILS Values('R2', 50);
Insert into LMS_FINE_DETAILS Values('R3', 75);
Insert into LMS_FINE_DETAILS Values('R4', 100);
Insert into LMS_FINE_DETAILS Values('R5', 150);
Insert into LMS_FINE_DETAILS Values('R6', 200);

Insert into LMS_BOOK_DETAILS
Values('BL000001', 'Java How To Do Program', 'JAVA', 'Paul J. Deitel', 'Prentice Hall', '1999-12-10', 6, 600.00, 'A1', '2011-05-10', 'S01');
Insert into LMS_BOOK_DETAILS
Values('BL000002', 'Java: The Complete Reference ', 'JAVA', 'Herbert Schildt', 'Tata Mcgraw Hill ', '2011-10-10', 5, 750.00, 'A1', '2011-05-10', 'S03');
Insert into LMS_BOOK_DETAILS
Values('BL000003', 'Java How To Do Program', 'JAVA', 'Paul J. Deitel', 'Prentice Hall', '1999-05-10', 6, 600.00, 'A1', '2012-05-10', 'S01');
Insert into LMS_BOOK_DETAILS
Values('BL000004', 'Java: The Complete Reference ', 'JAVA', 'Herbert Schildt', 'Tata Mcgraw Hill ', '2011-10-10', 5, 750.00, 'A1', '2012-05-11', 'S01');
Insert into LMS_BOOK_DETAILS
Values('BL000005', 'Java How To Do Program', 'JAVA', 'Paul J. Deitel', 'Prentice Hall', '1999-12-10', 6, 600.00, 'A1', '2012-05-11', 'S01');
Insert into LMS_BOOK_DETAILS
Values('BL000006', 'Java: The Complete Reference ', 'JAVA', 'Herbert Schildt', 'Tata Mcgraw Hill ', '2011-10-10', 5, 750.00, 'A1', '2012-05-12', 'S03');
Insert into LMS_BOOK_DETAILS
Values('BL000007', 'Let Us C', 'C', 'Yashavant Kanetkar ', 'BPB Publications', '2010-12-11', 9, 500.00 , 'A3', '2010-11-03', 'S03');
Insert into LMS_BOOK_DETAILS
Values('BL000008', 'Let Us C', 'C', 'Yashavant Kanetkar ','BPB Publications', '2010-05-12', 9, 500.00 , 'A3', '2011-08-09', 'S04');

Insert into LMS_BOOK_ISSUE
Values (001, 'LM001', 'BL000001', '2012-05-01', '2012-05-16', '2012-05-16', 'R0');
Insert into LMS_BOOK_ISSUE
Values (002, 'LM002', 'BL000002', '2012-05-01', '2012-05-06','2012-05-16', 'R2');
Insert into LMS_BOOK_ISSUE
Values (003, 'LM003', 'BL000007', '2012-04-01', '2012-04-16', '2012-04-20','R1');
Insert into LMS_BOOK_ISSUE
Values (004, 'LM004', 'BL000005', '2012-04-01', '2012-04-16','2012-04-20', 'R1');
Insert into LMS_BOOK_ISSUE
Values (005, 'LM005', 'BL000008', '2012-03-30', '2012-04-15','2012-04-20' , 'R1');
Insert into LMS_BOOK_ISSUE
Values (006, 'LM005', 'BL000008', '2012-04-20', '2012-05-05','2012-05-05' , 'R0');
Insert into LMS_BOOK_ISSUE
Values (007, 'LM003', 'BL000007', '2012-04-22', '2012-05-07','2012-05-25' , 'R4');
alter table lms_book_issue
add column book_issue_status varchar(10);

update lms_book_issue
set book_issue_status='yes'
where book_issue_no='1';

update lms_book_issue
set book_issue_status='yes'
where book_issue_no='6';

update lms_book_issue
set book_issue_status='no'
where book_issue_no='2';

update lms_book_issue
set book_issue_status='no'
where book_issue_no='3';

update lms_book_issue
set book_issue_status='no'
where book_issue_no='4';

update lms_book_issue
set book_issue_status='no'
where book_issue_no='5';

update lms_book_issue
set book_issue_status='no'
where book_issue_no='7';

SELECT MEMBER_ID,MEMBER_NAME,CITY,MEMBERSHIP_STATUS FROM LMS_MEMBERS WHERE MEMBERSHIP_STATUS='Permanent';
SELECT * FROM LMS_MEMBERS JOIN LMS_BOOK_ISSUE;
SELECT I.MEMBER_ID,M.MEMBER_NAME FROM LMS_MEMBERS M INNER JOIN LMS_BOOK_ISSUE I ON M.MEMBER_ID=I.MEMBER_ID WHERE BOOK_ISSUE_STATUS='No';
SELECT MEMBER_ID,MEMBER_NAME FROM LMS_MEMBERS INNER JOIN LMS_BOOK_DETAILS WHERE BOOK_CODE='BL000002';
SELECT BOOK_CODE,BOOK_TITLE FROM LMS_BOOK_DETAILS WHERE AUTHOR like 'P%';
SELECT COUNT(BOOK_CODE LIKE '%JAVA%') FROM LMS_BOOK_DETAILS;
select lms_book_details.book_title,lms_suppliers_details.supplier_id from lms_book_details,lms_suppliers_details
where author='Herbert Schildt' and book_edition = 5 and lms_suppliers_details.supplier_id ='S01';

select * from lms_book_details;

Problem # 1: Write a query to display the member id, member name, city and membership status who are all having life time membership. Hint: Life time membership status is "Permanent".

select memeber_id,member_name,city,membership_status from lms_members where membership_status='permanent';

product management system

create database ranjeet;

use ranjeet;
CREATE TABLE PMS_DEPARTMENT_DETAILS
(DEPARTMENT_ID int(2)PRIMARY KEY,DEPARTMENT_NAME VARCHAR(30)NOT NULL,
DEPARTMENT_LOCATION VARCHAR(30)NOT NULL,DEPARTMENT_EXTENSION int(3) NOT NULL);

CREATE TABLE PMS_MANAGER_DETAILS

(Manager_ID int(5) PRIMARY KEY,Manager_Name VARCHAR(30) NOT NULL,
Job VARCHAR(30) NOT NULL,Boss_Code int(5),Salary double(7,2) NOT NULL,
Commission int(5),DEPARTMENT_ID int(2));

CREATE TABLE PMS_UNIT_DETAILS

(UNIT_ID VARCHAR(2) PRIMARY KEY,UNIT_NAME VARCHAR(30) NOT NULL,
PIECE_WEIGHT VARCHAR(15) NOT NULL,TOTAL_PIECES int(3) NOT NULL,
UNIT_WEIGHT double(5,2) NOT NULL);

CREATE TABLE PMS_PRODUCT

(PRODUCT_ID VARCHAR(5) PRIMARY KEY,PRODUCT_NAME VARCHAR(30) NOT NULL,
DEPARTMENT_ID int(2));

CREATE TABLE PMS_PRODUCT_UNIT

(PRODUCT_ID VARCHAR(5),UNIT_ID VARCHAR(2));

CREATE TABLE PMS_MANUFACTURING

(MANFATURE_ID VARCHAR(5) PRIMARY KEY,PRODUCT_ID VARCHAR(5) NOT NULL,
UNIT_ID VARCHAR(5) NOT NULL,QUANTITY int(7) NOT NULL,AVAILABILITY VARCHAR(3) NOT NULL,
PRODUCT_MANFACTURE_DATE DATE NOT NULL,PRODUCT_EXPIRY_DATE DATE NOT NULL);

ALTER TABLE PMS_MANAGER_DETAILS ADD constraint FK_DEPARTMENT_ID1 foreign key(DEPARTMENT_ID) references PMS_DEPARTMENT_DETAILS(DEPARTMENT_ID);

ALTER TABLE PMS_PRODUCT ADD constraint FK_DEPARTMENT_ID2 foreign key(DEPARTMENT_ID) references PMS_DEPARTMENT_DETAILS(DEPARTMENT_ID);

ALTER TABLE PMS_PRODUCT_UNIT ADD constraint PK_Composite21 primary key(PRODUCT_ID,UNIT_ID);

ALTER TABLE PMS_PRODUCT_UNIT ADD constraint FK_PRODUCT_ID1 foreign key(PRODUCT_ID) references PMS_PRODUCT(PRODUCT_ID);

ALTER TABLE PMS_PRODUCT_UNIT ADD constraint FK_UNIT_ID1 foreign key(UNIT_ID) references PMS_UNIT_DETAILS(UNIT_ID);

ALTER TABLE PMS_MANUFACTURING ADD constraint FK_PRODUCT_ID2 foreign key(PRODUCT_ID) references PMS_PRODUCT(PRODUCT_ID);

ALTER TABLE PMS_MANUFACTURING ADD constraint FK_UNIT_ID2 foreign key(UNIT_ID) references PMS_UNIT_DETAILS(UNIT_ID);

INSERT INTO PMS_DEPARTMENT_DETAILS VALUES(10,'MIS','HYDERABAD_ZONE_1',121);

INSERT INTO PMS_DEPARTMENT_DETAILS VALUES(20,'GHEE SECTION','ONGOLE',122);
INSERT INTO PMS_DEPARTMENT_DETAILS VALUES(30,'PROCESSING SECTION','RAJAMUNDRY',123);
INSERT INTO PMS_DEPARTMENT_DETAILS VALUES(40,'BI_PRODUCTS SECTION','SECUNDERABAD',124);
INSERT INTO PMS_DEPARTMENT_DETAILS VALUES(50,'DISPATCH SECTION','HYDERABAD_ZONE_2',125);
INSERT INTO PMS_DEPARTMENT_DETAILS VALUES(60,'CUSTOMER CARE SECTION','HYDERABAD_ZONE_2',126);

INSERT INTO PMS_MANAGER_DETAILS VALUES(7711,'BLAKE','GENERAL MANAGER',NULL,25000,2500,10);

INSERT INTO PMS_MANAGER_DETAILS VALUES(7722,'LARANCE','DEPUTY GENERAL MANAGER',7711,28000,1500,10);
INSERT INTO PMS_MANAGER_DETAILS VALUES(7733,'GATES','MANAGER',7722,26750,500,20);
INSERT INTO PMS_MANAGER_DETAILS VALUES(7744,'CALRK','MANAGER',7722,22000,1000,30);
INSERT INTO PMS_MANAGER_DETAILS VALUES(7755,'VINCY','MANAGER',7722,17500,0,40);
INSERT INTO PMS_MANAGER_DETAILS VALUES(7766,'GALE','MANAGER',7722,16500,0,50);
INSERT INTO PMS_MANAGER_DETAILS VALUES(7770,'NANCY','ASSISTANT MANAGER',7733,30000,500,20);
INSERT INTO PMS_MANAGER_DETAILS VALUES(7771,'GOUD','ASSISTANT MANAGER',7744,23000,750,30);
INSERT INTO PMS_MANAGER_DETAILS VALUES(7772,'NAIDU','ASSISTANT MANAGER',7755,18500,0,40);
INSERT INTO PMS_MANAGER_DETAILS VALUES(7773,'RAO','ASSISTANT MANAGER',7766,15000,3000,50);
INSERT INTO PMS_MANAGER_DETAILS VALUES(7774,'RAJU','ASSISTANT MANAGER',7722,21050,2000,10);
INSERT INTO PMS_MANAGER_DETAILS VALUES(7775,'REDDY','ASSISTANT MANAGER',7722,28500,0,10);

INSERT INTO PMS_UNIT_DETAILS VALUES('C1','CARTON','235 ML/GMS',20,5);
INSERT INTO PMS_UNIT_DETAILS VALUES('M1','MIN_BOX','25 GMS',20,.5);
INSERT INTO PMS_UNIT_DETAILS VALUES('M2','MAX_BOX','25 GMS',40,1);
INSERT INTO PMS_UNIT_DETAILS VALUES('C2','CAN','19.7 KGS',1,20);
INSERT INTO PMS_UNIT_DETAILS VALUES('T1','TIN','30 GMS',50,1.5);
INSERT INTO PMS_UNIT_DETAILS VALUES('P1','PACK','980 ML',1,1);
INSERT INTO PMS_UNIT_DETAILS VALUES('P2','HALF_PACK','480 ML/GMS',1,0.5);
INSERT INTO PMS_UNIT_DETAILS VALUES('P3','CHOTA_PACK','235 ML/GMS',1,0.25);

INSERT INTO PMS_PRODUCT VALUES('P001','MIXED GHEE',20);

INSERT INTO PMS_PRODUCT VALUES('P002','PANNER',20);
INSERT INTO PMS_PRODUCT VALUES('P003','COOKING BUTTER',20);
INSERT INTO PMS_PRODUCT VALUES('P004','RASAGULLA',40);
INSERT INTO PMS_PRODUCT VALUES('P005','CURD',40);
INSERT INTO PMS_PRODUCT VALUES('P006','DIET MILK',30);
INSERT INTO PMS_PRODUCT VALUES('P007','TONNED MILK',30);
INSERT INTO PMS_PRODUCT VALUES('P008','FAMILY MILK',30);
INSERT INTO PMS_PRODUCT VALUES('P009','STANDERED MILK',30);
INSERT INTO PMS_PRODUCT VALUES('P010','WHOLE MILK',30);
INSERT INTO PMS_PRODUCT VALUES('P011','BUTTER MILK',40);
INSERT INTO PMS_PRODUCT VALUES('P012','DOODH PEDA',40);
INSERT INTO PMS_PRODUCT VALUES('P013','MILK SHAKE',40);

INSERT INTO PMS_PRODUCT_UNIT VALUES('P001','C2');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P001','P2');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P001','P3');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P001','P1');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P002','C1');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P002','P1');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P002','P2');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P002','P3');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P006','P1');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P006','P2');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P006','P3');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P007','P1');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P007','P2');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P007','P3');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P008','P1');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P008','P2');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P008','P3');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P009','P1');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P009','P2');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P009','P3');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P010','P1');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P010','P2');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P010','P3');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P003','P2');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P003','P3');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P004','T1');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P005','P3');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P011','P3');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P012','M1');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P012','M2');
INSERT INTO PMS_PRODUCT_UNIT VALUES('P013','C1');

INSERT INTO PMS_MANUFACTURING VALUES('M001','P001','C2',100,'YES','2012-08-15','2012-12-15');

INSERT INTO PMS_MANUFACTURING VALUES('M002','P001','P2',500,'YES','2012-08-10','2012-12-15');
INSERT INTO PMS_MANUFACTURING VALUES('M003','P001','P3',250,'YES','2012-08-10','2012-12-15');
INSERT INTO PMS_MANUFACTURING VALUES('M004','P001','P1',300,'NO','2012-08-15','2012-12-15');
INSERT INTO PMS_MANUFACTURING VALUES('M005','P002','C1',190,'YES','2012-08-5','2012-08-31');
INSERT INTO PMS_MANUFACTURING VALUES('M006','P002','P1',500,'YES','2012-08-5','2012-08-31');
INSERT INTO PMS_MANUFACTURING VALUES('M007','P002','P2',250,'YES','2012-08-5','2012-08-31');
INSERT INTO PMS_MANUFACTURING VALUES('M008','P002','P3',500,'YES','2012-08-5','2012-08-31');
INSERT INTO PMS_MANUFACTURING VALUES('M009','P006','P1',4500,'YES','2012-08-15','2012-08-16');
INSERT INTO PMS_MANUFACTURING VALUES('M010','P006','P2',7500,'YES','2012-08-15','2012-08-16');
INSERT INTO PMS_MANUFACTURING VALUES('M011','P006','P3',10000,'YES','2012-08-15','2012-08-16');
INSERT INTO PMS_MANUFACTURING VALUES('M012','P007','P1',4000,'YES','2012-08-15','2012-08-16');
INSERT INTO PMS_MANUFACTURING VALUES('M013','P007','P2',3000,'YES','2012-08-15','2012-08-16');
INSERT INTO PMS_MANUFACTURING VALUES('M014','P007','P3',2500,'YES','2012-08-15','2012-08-16');
INSERT INTO PMS_MANUFACTURING VALUES('M015','P008','P1',7000,'NO','2012-08-15','2012-08-16');
INSERT INTO PMS_MANUFACTURING VALUES('M016','P008','P2',3500,'YES','2012-08-15','2012-08-16');
INSERT INTO PMS_MANUFACTURING VALUES('M017','P008','P3',4500,'YES','2012-08-15','2012-08-16');
INSERT INTO PMS_MANUFACTURING VALUES('M018','P009','P1',1500,'NO','2012-08-15','2012-08-16');
INSERT INTO PMS_MANUFACTURING VALUES('M019','P009','P2',2500,'YES','2012-08-15','2012-08-16');
INSERT INTO PMS_MANUFACTURING VALUES('M020','P009','P3',1000,'YES','2012-08-15','2012-08-16');
INSERT INTO PMS_MANUFACTURING VALUES('M021','P010','P1',10000,'YES','2012-08-15','2012-08-16');
INSERT INTO PMS_MANUFACTURING VALUES('M022','P010','P2',25000,'YES','2012-08-15','2012-08-16');
INSERT INTO PMS_MANUFACTURING VALUES('M023','P010','P3',12500,'YES','2012-08-15','2012-08-16');
INSERT INTO PMS_MANUFACTURING VALUES('M024','P003','P2',2400,'YES','2012-08-15','2012-10-10');
INSERT INTO PMS_MANUFACTURING VALUES('M025','P003','P3',3210,'NO','2012-08-15','2012-10-10');
INSERT INTO PMS_MANUFACTURING VALUES('M026','P004','T1',750,'YES','2012-08-15','2012-10-10');
INSERT INTO PMS_MANUFACTURING VALUES('M027','P005','P3',10000,'YES','2012-08-15','2012-08-17');
INSERT INTO PMS_MANUFACTURING VALUES('M028','P011','P3',27500,'YES','2012-08-15','2012-08-16');
INSERT INTO PMS_MANUFACTURING VALUES('M029','P012','M1',2750,'YES','2012-08-15','2012-08-31');
INSERT INTO PMS_MANUFACTURING VALUES('M030','P012','M2',1850,'NO','2012-08-15','2012-08-31');
INSERT INTO PMS_MANUFACTURING VALUES('M031','P013','C1',1300,'YES','2012-08-10','2012-08-11');

select

    a.Manager_id, b.manager_name, a.job,a. manager_name as boss_name
from
    PMs_manager_details a
         join
    pms_manager_details b ON a.manager_id = b.boss_code;

select *from pms_manager_details;

select manager_id,manager_name,job,salary,commission,department_id

from pms_manager_details where salary=(select salary from pms_manager_details group by salary order by salary desc limit 1,1);

select salary from pms_manager_details group by salary order by salary desc;

update pms_manager_details set salary=28500 where manager_id=7711;

select manager_id,manager_name,job,salary from pms_manager_details

where manager_name like '_a%' and salary > all(select max(salary) from pms_manager_details where manager_name like 'v%');

(select manager_name from pms_manager_details where manager_name like'_a%');

select manager_id,manager_name,job,salary,(salary*(salary*12*(7.5/100)))as net_salary from pms_manager_details;

select a.manfature_id,b.product_name,a.unit_id,a.quantity,a.product_manfacture_date,a.product_expiry_date from pms_manufacturing a inner join pms_product b on a.product_id=b.product_id inner join pms_manager_details c on c.department_id=b.department_id inner join pms_department_details d on c.department_id=d.department_id where department_name='GHEE SECTION';

select * from pms_manufacturing;

select sum(quantity) from pms_manufacturing group by product_id;

select a.product_name ,b.availability from pms_product a inner join pms_manufacturing b on

a.product_id=b.product_id where product_expiry_date='2012-12-15';

select * from pms_manager_details;

-------------------------------------*****-------------------------------

Case Study

Problem Scenario: A Dairy Milk Organization wants to develop a data base Product Management System (PMS) which maintains about the various products processed and their stock details along with this it also has to maintain the various departments and their manager details.

List of Tables:

Table #1: PMS_DEPARTMENT_DETAILS

Table #2: PMS_MANAGER_DETAILS

Table #3: PMS_UNIT_DETAILS

Table #4: PMS_PRODUCT

Table #5: PMS_PRODUCT_UNIT

Table #6: PMS_MANUFACTURING

ER Diagram:

Database and Table Creations - DDL Queries:

Loading Data - DML Queries:

simple Questions:

Problem # 1:

List the Managers and their respective Boss Names. In select list we have Manager_ID, Manager_Name, Job, and Boss_Name.

Hint: PMS_MANAGER_DETAILS alias as Manager

PMS_MANAGER_DETAILS alias as Boss

Boss_Name as an alias in the select query.

Solution:

Problem # 2:

Find the 2^nd Highest Salary Earner of all the Managers. In select list we have Manager_ID,Manager_Name,Job,Salary,Commission,Department_ID

Solution:

Problem # 3:

Find out the Employees whose name has letter ‘A’ in the 2^nd position & are earning more than the salary whose name starts with ‘V’. In select list we have MANAGER_ID, MANAGER_NAME, JOB, and SALARY.

Solution:

Problem # 4:

Add 7.5% of salary as performance bonus for each employee and display the net yearly salary of each employee. (Do not update the database.). In select list we have MANAGER_ID, MANAGER_NAME, JOB, and YEARLY_SALARY.

YEARLY_SALARY as an alias for the expression used in select query.

Solution:

Problem # 5:

Display the Manufactured products details that are belong to ‘GHEE SECTION’. In select list we have MANFATURE_ID, PRODUCT_NAME, UNIT_ID, QUANTITY, PRODUCT_MANFACTURE_DATE, and PRODUCT_EXPIRY_DATE.

Hint: PMS_MANUFACTURING TABLE ALIAS AS M

PMS_PRODUCT TABLE ALIAS AS P

Solution:

Problem # 6:

FIND SUM OF QUANTITY WITH RESPECT TO EACH QUANTITY.

Solution:

Problem # 7:

Find the list of products which are available as on ’15-DEC-12’.

Solution:

Problem # 8:

Find the list of products along with their count which are not available as on ’15-DEC-12’. In select query COUNT_PRODUCT as an alias for count field.

Solution:

Problem # 9:

Find the employees with higher salary than the average salary of ‘MANAGER’ and those are all not MANAGER’S. In select list we have Manager_ID, Manager_Name and their Job details.

Solution:

Problem # 10:

Display the manager details who are drawing the salary more than 20000 Rs/-. And we need to display the manager name in proper case and order by department wise. In select list we have Manager_ID, Manager_Name and Department_ID.

Solution:

Average Questions:

Problem # 1:

Write a query to display the Department_Name, Department_Location whose number of employees are more than or equal to 4

Solution:

Problem # 2:

Find the Persons with a Job which has the highest average salary. In select list we have MANAGER_ID, MANAGER_NAME, JOB, and SALARY.

Solution:

Problem # 3:

List the employees who earn more than their own department’s average salary and display them in Department_ID order. In select list we have MANAGER_ID, MANAGER_NAME, JOB, SALARY, and DEPARTMENT_ID.

Solution:

Problem # 4:

Display the Product details that are ends with MILK. In select list we have to display PRODUCT_ID, PRODUCT_NAME, UNIT_ID, and UNIT_WEIGHT.

Hint: PMS_PRODUCT Table Alias as P

PMS_PRODUCT_UNIT Table Alias as U

PMS_UNIT_DETAILS Table Alias as D

Solution:

Problem # 5:

Display the Product Name’s along with their possible packing details in the order of Weight. In select list we have to display PRODUCT_NAME, UNIT_NAME, TOTAL_PIECES, and UNIT_WEIGHT.

Hint: PMS_PRODUCT Table Alias as P

PMS_PRODUCT_UNIT Table Alias as U

PMS_UNIT_DETAILS Table Alias as D

Solution:

Problem # 6:

Display the product_id and sum of quantity whose available status is ‘yes’. And sum of quantity greater than 1500.

Hint: TOTAL_QUANTITY is an Alias name used in the select query.

Solution:

Problem # 7:

Display Product_ID, Product_Name, Department_ID and the number of varieties of units available as on ’15-dec-12’.

Hint: NUMBER_VARIETIES as an Alias name for Count field in select query.

PMS_MANUFACTURING Table Alias as M

PMS_PRODUCT Table Alias as P

Solution:

Problem # 8:

List the employees earning more than the average salary of employees based out of ONGOLE. In select list we have MANAGER_ID and MANAGER_NAME

Solution:

Problem#9:

List the MANAGER’s who have salary higher than that of the department with highest number of employees. In select list we have MANAGER_ID, MANAGER_NAME, JOB and SALARY.

Solution:

Problem#10:

List the departments which does not have any employees. In select query we have DEPARTMENT_ID,DEPARTMENT_NAME

Solution:

Complex Questions:

Problem # 1:

Display the details of manager who is drawing the 7^th highest salary among all the other managers. In select list we have MANAGER_ID,MANAGER_NAME,JOB,SALARY and DEPARTMENT_ID

Solution:

Problem # 2:

Write a query to find the list of all the sub ordinates whose salary is greater than the respective boss salary. In select list we have S.MANAGER_ID,S.MANAGER_NAME,S.JOB,S.SALARY,S.DEPARTMENT_ID,B.MANAGER_ID,B.MANAGER_NAME,B.SALARY

Hint: PMS_MANAGER_DETAILS Table Alias as S

PMS_MANAGER_DETAILS Table Alias as B

Solution:

Problem # 3:

Find the number of batches manufactured greater than 5 in each month

Displaying the Month name and Number of batches.

Hint: MONTH AS AN ALIAS NAME FOR MONTH NAME

NUMBER_BATCHES AS AN ALIAS NAME FOR COUNT FIELD IN THE SELECT QUERY

Solution:

Problem # 4:

Display the PRODUCT_ID, PRODUCT_NAME, and DEPARTMENT_ID which is manufactured maximum with respect to sum of quantity.

Solution:

Problem # 5:

Find the maximum quantity manufactured with respect to each product and display the product id, product name and Quantity from each product.

Hint: PMS_MANUFACTURING Table Alias as M

PMS_PRODUCT Table Alias as PP

Solution:

Hands-on Exercise: ANSI SQL Functions

Session 05

Version: ANSI SQL/Hands-on Exercise/05/1.0

Date: 14-02-2013

Cognizant

500 Glen Pointe Center West

Teaneck, NJ 07666

Ph: 201-801-0233www.cognizant.com

TABLE OF CONTENTS

ANSI SQL 05: ANSI SQL Functions

Exercise.1

ANSI SQL 05: ANSI SQL Functions

Pre-requisites: Tables should be created and data should be loaded into the tables.

Exercise.1

Estimated Completion Time: 60 Minutes

Hands-on Exercise Objective
After completing the hands-on exercises, you will be able to: Write queries using ANSI SQL Functions.

Problem Statement:

Problem # 1:

Write a query to display student information such as name, branch in capital letters.

Problem # 2:

Write a query to displays all details in subject_master in small letters.

Problem # 3:

Write two separate queries to display the registration number date of birth of all the students in the following formats

• 2011/07/23

• July 23, 2011

Problem # 4:

Write a query to display age of each student along with name, contact number and email id.

Age = Number of months between DOB and current date /12.

Problem # 5:

Write a query to display the registration number, student name and average marks secured by students in each semester.

Problem # 6:

Pick the maximum mark from the students_marks and display the student registration number and name of those students who have secured the maximum mark..

Problem # 7:

Pick the maximum marks secured in the subject “ EI05IP” and display the student name and registration number of the student who has secured it.

Problem # 8:

Write a query to display the average GPA for each semester. Display the semester number and the average.

Hint: Average = Total GPA of all students in a semester/total number of students in a semester

Rule: Use AVG function

Problem # 9:

Write a query which will display all the student records, if the student email id is null it should be displayed as “no valid email address”.

Problem # 10:

Write a query which will display the student name, branch, registration number, semester number and result. Display the full name of EEE as well as ECE branch as mentioned below,

If EEE then ‘Electrical and Electronic Engineering’

If ECE then Electronics and Communication Engineering.

Deliverables Expected:

Solution Queries

Tips:

Funtion	Description	Example	Output	Reference
DATE_FORMAT(date,format)	Formats the *date* value according to the *format* string.	SELECT DATE_FORMAT('2009-10-04', '%W %M %Y');	Sunday October 2009	https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format
		SELECT DATE_FORMAT('2009-10-04', '%d-%b-%Y');	4-Oct-2009
		SELECT DATE_FORMAT('2009-10-04', '%d-%m-%Y');	4-10-2009
STR_TO_DATE(str,format)	Formats a String to currosponding date	SELECT STR_TO_DATE('01-5-2013','%d-%m-%Y');	5/1/2013	https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format
EXTRACT(unit FROM date)	extracts parts from the date	SELECT EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03');	200907	https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_extract
PERIOD_DIFF(period1,period2)	Return the number of months between periods	SELECT PERIOD_DIFF(200802,200703);	11	https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_period-diff
FORMAT (N, D)	converts a number to a format like ‘#,###,###.##’ which is rounded upto the number of decimal places specified (in the second argument) and returns the result as a string.	SELECT FORMAT(12324.2573,3);	12,324.26	http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_format
CONCAT(str1,str2)	Return concatenated string	SELECT CONCAT('My', 'SQL');	MySQL	http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
SUBSTR()	Return the substring as specified	SELECT SUBSTRING ('Quadratically',5,6);	'ratica'	http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr
ROUND()	Round the argument	SELECT ROUND(1.298, 1);	1.3	http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_round
TRUNCATE()	Truncate to specified number of decimal places	SELECT TRUNCATE(1.223,1);	1.2	http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_truncate
CASE	CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END	SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;	TRUE
IF	IF(expr1,expr2,expr3)	SELECT IF(1<2,'yes','no');	yes
IFNULL	IFNULL(expr1,expr2)	SELECT IFNULL(NULL,5);	5

practice questions :

------------------------------------------------------------------------------------------------------------------------------------------------------------------

1) employee detail

CREATE TABLE Departments (
   Code INTEGER PRIMARY KEY NOT NULL, Name TEXT NOT NULL ,
   Budget REAL NOT NULL
);

CREATE TABLE Employees (
   SSN INTEGER PRIMARY KEY NOT NULL,
   Name TEXT NOT NULL ,
   LastName TEXT NOT NULL ,
   Department INTEGER NOT NULL ,
   CONSTRAINT fk_Departments_Code FOREIGN KEY(Department)
   REFERENCES Departments(Code)
);

INSERT INTO Departments(Code,Name,Budget) VALUES(14,'IT',65000);
INSERT INTO Departments(Code,Name,Budget) VALUES(37,'Accounting',15000);
INSERT INTO Departments(Code,Name,Budget) VALUES(59,'Human Resources',240000);
INSERT INTO Departments(Code,Name,Budget) VALUES(77,'Research',55000);

INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('123234877','Michael','Rogers',14);
INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('152934485','Anand','Manikutty',14);
INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('222364883','Carol','Smith',37);
INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('326587417','Joe','Stevens',37);
INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('332154719','Mary-Anne','Foster',14);
INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('332569843','George','ODonnell',77);
INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('546523478','John','Doe',59);
INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('631231482','David','Smith',77);
INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('654873219','Zacary','Efron',59);
INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('745685214','Eric','Goldsmith',59);
INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('845657245','Elizabeth','Doe',14);
INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('845657246','Kumar','Swamy',14);

Select the last name of all employees.

Click to see solution

SELECT LastName FROM Employees;

Select the last name of all employees, without duplicates.

Click to see solution

SELECT DISTINCT LastName FROM Employees;

Select all the data of employees whose last name is "Smith".

Click to see solution

SELECT * FROM Employees WHERE LastName = 'Smith';

Select all the data of employees whose last name is "Smith" or "Doe".

Click to see solution

/* With OR */
SELECT * FROM Employees
  WHERE LastName = 'Smith' OR LastName = 'Doe';

/* With IN */
SELECT * FROM Employees
  WHERE LastName IN ('Smith' , 'Doe');

Select all the data of employees that work in department 14.

Click to see solution

SELECT * FROM Employees WHERE Department = 14;

Select all the data of employees that work in department 37 or department 77.

Click to see solution

/* With OR */
SELECT * FROM Employees
  WHERE Department = 37 OR Department = 77;

/* With IN */
SELECT * FROM Employees
  WHERE Department IN (37,77);

Select all the data of employees whose last name begins with an "S".

Click to see solution

SELECT * FROM Employees
  WHERE LastName LIKE 'S%';

Select the sum of all the departments' budgets.

Click to see solution

SELECT SUM(Budget) FROM Departments;

Select the number of employees in each department (you only need to show the department code and the number of employees).

Click to see solution

SELECT Department, COUNT(*)
  FROM Employees
  GROUP BY Department;

Select all the data of employees, including each employee's department's data.

Click to see solution

SELECT *
  FROM Employees INNER JOIN Departments
  ON Employees.Department = Departments.Code;

Select the name and last name of each employee, along with the name and budget of the employee's department.

Click to see solution

/* Without labels */
SELECT Employees.Name, LastName, Departments.Name AS DepartmentsName, Budget
  FROM Employees INNER JOIN Departments
  ON Employees.Department = Departments.Code;

/* With labels */
SELECT E.Name, LastName, D.Name AS DepartmentsName, Budget
  FROM Employees E INNER JOIN Departments D
  ON E.Department = D.Code;

Select the name and last name of employees working for departments with a budget greater than $60,000.

Click to see solution

/* Without subquery */
SELECT Employees.Name, LastName
  FROM Employees INNER JOIN Departments
  ON Employees.Department = Departments.Code
    AND Departments.Budget > 60000;

/* With subquery */
SELECT Name, LastName FROM Employees
  WHERE Department IN
  (SELECT Code FROM Departments WHERE Budget > 60000);

Select the departments with a budget larger than the average budget of all the departments.

Click to see solution

SELECT *
  FROM Departments
  WHERE Budget >
  (
    SELECT AVG(Budget)
    FROM Departments
  );

Select the names of departments with more than two employees.

Click to see solution

/* With subquery */
SELECT Name FROM Departments
  WHERE Code IN
  (
    SELECT Department
      FROM Employees
      GROUP BY Department
      HAVING COUNT(*) > 2
  );

/* With UNION. This assumes that no two departments have
   the same name */
SELECT Departments.Name
  FROM Employees INNER JOIN Departments
  ON Department = Code
  GROUP BY Departments.Name
  HAVING COUNT(*) > 2;

Select the name and last name of employees working for departments with second lowest budget.

Click to see solution

/* With subquery */
SELECT Name, LastName FROM Employees
  WHERE Department IN
  (SELECT Code FROM Departments WHERE Budget = (

SELECT TOP 1 Budget FROM Departments WHERE Budget IN ( SELECT DISTINCT TOP 2 Budget FROM Departments ORDER BY Budget ASC ) ORDER BY Budget DESC

   )
 );

Add a new department called "Quality Assurance", with a budger of $40,000 and departmental code 11. Add an employee called "Mary Moore" in that department, with SSN 847-2198-110.

Click to see solution

INSERT INTO Departments
  VALUES ( 11 , 'Calidad' , 40000);

INSERT INTO Employees
  VALUES ( '89267109' , 'Mary' , 'Moore' , 11);

Reduce the budget of all departments by 10%.

Click to see solution

UPDATE Departments SET Budget = Budget * 0.9;

Reassign all employees from the Research department (code 77) to the IT department (code 14).

Click to see solution

UPDATE Employees SET Department = 14 WHERE Department = 77;

Delete from the table all employees in the IT department (code 14).

Click to see solution

DELETE FROM Employees
  WHERE Department = 14;

Delete from the table all employees who work in departments with a budget greater than or equal to $60,000.

Click to see solution

DELETE FROM Employees
  WHERE Department IN
  (
    SELECT Code FROM Departments
      WHERE Budget >= 60000
  );

------------------------------------------------------------------------------------------------------------------------------------------------------------------

hospital management system:

CREATE TABLE Physician (
EmployeeID INTEGER PRIMARY KEY NOT NULL,
Name TEXT NOT NULL,
Position TEXT NOT NULL,
SSN INTEGER NOT NULL
);

CREATE TABLE Department (
DepartmentID INTEGER PRIMARY KEY NOT NULL,
Name TEXT NOT NULL,
Head INTEGER NOT NULL
    CONSTRAINT fk_Physician_EmployeeID REFERENCES Physician(EmployeeID)
);

CREATE TABLE Affiliated_With (
Physician INTEGER NOT NULL
    CONSTRAINT fk_Physician_EmployeeID REFERENCES Physician(EmployeeID),
Department INTEGER NOT NULL
    CONSTRAINT fk_Department_DepartmentID REFERENCES Department(DepartmentID),
PrimaryAffiliation BOOLEAN NOT NULL,
PRIMARY KEY(Physician, Department)
);

CREATE TABLE Procedure (
Code INTEGER PRIMARY KEY NOT NULL,
Name TEXT NOT NULL,
Cost REAL NOT NULL
);

CREATE TABLE Trained_In (
Physician INTEGER NOT NULL
    CONSTRAINT fk_Physician_EmployeeID REFERENCES Physician(EmployeeID),
Treatment INTEGER NOT NULL
    CONSTRAINT fk_Procedure_Code REFERENCES Procedure(Code),
CertificationDate DATETIME NOT NULL,
CertificationExpires DATETIME NOT NULL,
PRIMARY KEY(Physician, Treatment)
);

CREATE TABLE Patient (
SSN INTEGER PRIMARY KEY NOT NULL,
Name TEXT NOT NULL,
Address TEXT NOT NULL,
Phone TEXT NOT NULL,
InsuranceID INTEGER NOT NULL,
PCP INTEGER NOT NULL
    CONSTRAINT fk_Physician_EmployeeID REFERENCES Physician(EmployeeID)
);

CREATE TABLE Nurse (
EmployeeID INTEGER PRIMARY KEY NOT NULL,
Name TEXT NOT NULL,
Position TEXT NOT NULL,
Registered BOOLEAN NOT NULL,
SSN INTEGER NOT NULL
);

CREATE TABLE Appointment (
AppointmentID INTEGER PRIMARY KEY NOT NULL,
Patient INTEGER NOT NULL
    CONSTRAINT fk_Patient_SSN REFERENCES Patient(SSN),
PrepNurse INTEGER
    CONSTRAINT fk_Nurse_EmployeeID REFERENCES Nurse(EmployeeID),
Physician INTEGER NOT NULL
    CONSTRAINT fk_Physician_EmployeeID REFERENCES Physician(EmployeeID),
Start DATETIME NOT NULL,
End DATETIME NOT NULL,
ExaminationRoom TEXT NOT NULL
);

CREATE TABLE Medication (
Code INTEGER PRIMARY KEY NOT NULL,
Name TEXT NOT NULL,
Brand TEXT NOT NULL,
Description TEXT NOT NULL
);

CREATE TABLE Prescribes (
Physician INTEGER NOT NULL
    CONSTRAINT fk_Physician_EmployeeID REFERENCES Physician(EmployeeID),
Patient INTEGER NOT NULL
    CONSTRAINT fk_Patient_SSN REFERENCES Patient(SSN),
Medication INTEGER NOT NULL
    CONSTRAINT fk_Medication_Code REFERENCES Medication(Code),
Date DATETIME NOT NULL,
Appointment INTEGER
    CONSTRAINT fk_Appointment_AppointmentID REFERENCES Appointment(AppointmentID),
Dose TEXT NOT NULL,
PRIMARY KEY(Physician, Patient, Medication, Date)
);

CREATE TABLE Block (
Floor INTEGER NOT NULL,
Code INTEGER NOT NULL,
PRIMARY KEY(Floor, Code)
);

CREATE TABLE Room (
Number INTEGER PRIMARY KEY NOT NULL,
Type TEXT NOT NULL,
BlockFloor INTEGER NOT NULL
    CONSTRAINT fk_Block_Floor REFERENCES Block(Floor),
BlockCode INTEGER NOT NULL
    CONSTRAINT fk_Block_Code REFERENCES Block(Code),
Unavailable BOOLEAN NOT NULL
);

CREATE TABLE On_Call (
Nurse INTEGER NOT NULL
    CONSTRAINT fk_Nurse_EmployeeID REFERENCES Nurse(EmployeeID),
BlockFloor INTEGER NOT NULL
    CONSTRAINT fk_Block_Floor REFERENCES Block(Floor),
BlockCode INTEGER NOT NULL
    CONSTRAINT fk_Block_Code REFERENCES Block(Code),
Start DATETIME NOT NULL,
End DATETIME NOT NULL,
PRIMARY KEY(Nurse, BlockFloor, BlockCode, Start, End)
);

CREATE TABLE Stay (
StayID INTEGER PRIMARY KEY NOT NULL,
Patient INTEGER NOT NULL
    CONSTRAINT fk_Patient_SSN REFERENCES Patient(SSN),
Room INTEGER NOT NULL
    CONSTRAINT fk_Room_Number REFERENCES Room(Number),
Start DATETIME NOT NULL,
End DATETIME NOT NULL
);

CREATE TABLE Undergoes (
Patient INTEGER NOT NULL
    CONSTRAINT fk_Patient_SSN REFERENCES Patient(SSN),
Procedure INTEGER NOT NULL
    CONSTRAINT fk_Procedure_Code REFERENCES Procedure(Code),
Stay INTEGER NOT NULL
    CONSTRAINT fk_Stay_StayID REFERENCES Stay(StayID),
Date DATETIME NOT NULL,
Physician INTEGER NOT NULL
    CONSTRAINT fk_Physician_EmployeeID REFERENCES Physician(EmployeeID),
AssistingNurse INTEGER
    CONSTRAINT fk_Nurse_EmployeeID REFERENCES Nurse(EmployeeID),
PRIMARY KEY(Patient, Procedure, Stay, Date)
);

INSERT INTO Physician VALUES(1,'John Dorian','Staff Internist',111111111);
INSERT INTO Physician VALUES(2,'Elliot Reid','Attending Physician',222222222);
INSERT INTO Physician VALUES(3,'Christopher Turk','Surgical Attending Physician',333333333);
INSERT INTO Physician VALUES(4,'Percival Cox','Senior Attending Physician',444444444);
INSERT INTO Physician VALUES(5,'Bob Kelso','Head Chief of Medicine',555555555);
INSERT INTO Physician VALUES(6,'Todd Quinlan','Surgical Attending Physician',666666666);
INSERT INTO Physician VALUES(7,'John Wen','Surgical Attending Physician',777777777);
INSERT INTO Physician VALUES(8,'Keith Dudemeister','MD Resident',888888888);
INSERT INTO Physician VALUES(9,'Molly Clock','Attending Psychiatrist',999999999);

INSERT INTO Department VALUES(1,'General Medicine',4);
INSERT INTO Department VALUES(2,'Surgery',7);
INSERT INTO Department VALUES(3,'Psychiatry',9);

INSERT INTO Affiliated_With VALUES(1,1,1);
INSERT INTO Affiliated_With VALUES(2,1,1);
INSERT INTO Affiliated_With VALUES(3,1,0);
INSERT INTO Affiliated_With VALUES(3,2,1);
INSERT INTO Affiliated_With VALUES(4,1,1);
INSERT INTO Affiliated_With VALUES(5,1,1);
INSERT INTO Affiliated_With VALUES(6,2,1);
INSERT INTO Affiliated_With VALUES(7,1,0);
INSERT INTO Affiliated_With VALUES(7,2,1);
INSERT INTO Affiliated_With VALUES(8,1,1);
INSERT INTO Affiliated_With VALUES(9,3,1);

INSERT INTO Procedure VALUES(1,'Reverse Rhinopodoplasty',1500.0);
INSERT INTO Procedure VALUES(2,'Obtuse Pyloric Recombobulation',3750.0);
INSERT INTO Procedure VALUES(3,'Folded Demiophtalmectomy',4500.0);
INSERT INTO Procedure VALUES(4,'Complete Walletectomy',10000.0);
INSERT INTO Procedure VALUES(5,'Obfuscated Dermogastrotomy',4899.0);
INSERT INTO Procedure VALUES(6,'Reversible Pancreomyoplasty',5600.0);
INSERT INTO Procedure VALUES(7,'Follicular Demiectomy',25.0);

INSERT INTO Patient VALUES(100000001,'John Smith','42 Foobar Lane','555-0256',68476213,1);
INSERT INTO Patient VALUES(100000002,'Grace Ritchie','37 Snafu Drive','555-0512',36546321,2);
INSERT INTO Patient VALUES(100000003,'Random J. Patient','101 Omgbbq Street','555-1204',65465421,2);
INSERT INTO Patient VALUES(100000004,'Dennis Doe','1100 Foobaz Avenue','555-2048',68421879,3);

INSERT INTO Nurse VALUES(101,'Carla Espinosa','Head Nurse',1,111111110);
INSERT INTO Nurse VALUES(102,'Laverne Roberts','Nurse',1,222222220);
INSERT INTO Nurse VALUES(103,'Paul Flowers','Nurse',0,333333330);

INSERT INTO Appointment VALUES(13216584,100000001,101,1,'2008-04-24 10:00','2008-04-24 11:00','A');
INSERT INTO Appointment VALUES(26548913,100000002,101,2,'2008-04-24 10:00','2008-04-24 11:00','B');
INSERT INTO Appointment VALUES(36549879,100000001,102,1,'2008-04-25 10:00','2008-04-25 11:00','A');
INSERT INTO Appointment VALUES(46846589,100000004,103,4,'2008-04-25 10:00','2008-04-25 11:00','B');
INSERT INTO Appointment VALUES(59871321,100000004,NULL,4,'2008-04-26 10:00','2008-04-26 11:00','C');
INSERT INTO Appointment VALUES(69879231,100000003,103,2,'2008-04-26 11:00','2008-04-26 12:00','C');
INSERT INTO Appointment VALUES(76983231,100000001,NULL,3,'2008-04-26 12:00','2008-04-26 13:00','C');
INSERT INTO Appointment VALUES(86213939,100000004,102,9,'2008-04-27 10:00','2008-04-21 11:00','A');
INSERT INTO Appointment VALUES(93216548,100000002,101,2,'2008-04-27 10:00','2008-04-27 11:00','B');

INSERT INTO Medication VALUES(1,'Procrastin-X','X','N/A');
INSERT INTO Medication VALUES(2,'Thesisin','Foo Labs','N/A');
INSERT INTO Medication VALUES(3,'Awakin','Bar Laboratories','N/A');
INSERT INTO Medication VALUES(4,'Crescavitin','Baz Industries','N/A');
INSERT INTO Medication VALUES(5,'Melioraurin','Snafu Pharmaceuticals','N/A');

INSERT INTO Prescribes VALUES(1,100000001,1,'2008-04-24 10:47',13216584,'5');
INSERT INTO Prescribes VALUES(9,100000004,2,'2008-04-27 10:53',86213939,'10');
INSERT INTO Prescribes VALUES(9,100000004,2,'2008-04-30 16:53',NULL,'5');

INSERT INTO Block VALUES(1,1);
INSERT INTO Block VALUES(1,2);
INSERT INTO Block VALUES(1,3);
INSERT INTO Block VALUES(2,1);
INSERT INTO Block VALUES(2,2);
INSERT INTO Block VALUES(2,3);
INSERT INTO Block VALUES(3,1);
INSERT INTO Block VALUES(3,2);
INSERT INTO Block VALUES(3,3);
INSERT INTO Block VALUES(4,1);
INSERT INTO Block VALUES(4,2);
INSERT INTO Block VALUES(4,3);

INSERT INTO Room VALUES(101,'Single',1,1,0);
INSERT INTO Room VALUES(102,'Single',1,1,0);
INSERT INTO Room VALUES(103,'Single',1,1,0);
INSERT INTO Room VALUES(111,'Single',1,2,0);
INSERT INTO Room VALUES(112,'Single',1,2,1);
INSERT INTO Room VALUES(113,'Single',1,2,0);
INSERT INTO Room VALUES(121,'Single',1,3,0);
INSERT INTO Room VALUES(122,'Single',1,3,0);
INSERT INTO Room VALUES(123,'Single',1,3,0);
INSERT INTO Room VALUES(201,'Single',2,1,1);
INSERT INTO Room VALUES(202,'Single',2,1,0);
INSERT INTO Room VALUES(203,'Single',2,1,0);
INSERT INTO Room VALUES(211,'Single',2,2,0);
INSERT INTO Room VALUES(212,'Single',2,2,0);
INSERT INTO Room VALUES(213,'Single',2,2,1);
INSERT INTO Room VALUES(221,'Single',2,3,0);
INSERT INTO Room VALUES(222,'Single',2,3,0);
INSERT INTO Room VALUES(223,'Single',2,3,0);
INSERT INTO Room VALUES(301,'Single',3,1,0);
INSERT INTO Room VALUES(302,'Single',3,1,1);
INSERT INTO Room VALUES(303,'Single',3,1,0);
INSERT INTO Room VALUES(311,'Single',3,2,0);
INSERT INTO Room VALUES(312,'Single',3,2,0);
INSERT INTO Room VALUES(313,'Single',3,2,0);
INSERT INTO Room VALUES(321,'Single',3,3,1);
INSERT INTO Room VALUES(322,'Single',3,3,0);
INSERT INTO Room VALUES(323,'Single',3,3,0);
INSERT INTO Room VALUES(401,'Single',4,1,0);
INSERT INTO Room VALUES(402,'Single',4,1,1);
INSERT INTO Room VALUES(403,'Single',4,1,0);
INSERT INTO Room VALUES(411,'Single',4,2,0);
INSERT INTO Room VALUES(412,'Single',4,2,0);
INSERT INTO Room VALUES(413,'Single',4,2,0);
INSERT INTO Room VALUES(421,'Single',4,3,1);
INSERT INTO Room VALUES(422,'Single',4,3,0);
INSERT INTO Room VALUES(423,'Single',4,3,0);

INSERT INTO On_Call VALUES(101,1,1,'2008-11-04 11:00','2008-11-04 19:00');
INSERT INTO On_Call VALUES(101,1,2,'2008-11-04 11:00','2008-11-04 19:00');
INSERT INTO On_Call VALUES(102,1,3,'2008-11-04 11:00','2008-11-04 19:00');
INSERT INTO On_Call VALUES(103,1,1,'2008-11-04 19:00','2008-11-05 03:00');
INSERT INTO On_Call VALUES(103,1,2,'2008-11-04 19:00','2008-11-05 03:00');
INSERT INTO On_Call VALUES(103,1,3,'2008-11-04 19:00','2008-11-05 03:00');

INSERT INTO Stay VALUES(3215,100000001,111,'2008-05-01','2008-05-04');
INSERT INTO Stay VALUES(3216,100000003,123,'2008-05-03','2008-05-14');
INSERT INTO Stay VALUES(3217,100000004,112,'2008-05-02','2008-05-03');

INSERT INTO Undergoes VALUES(100000001,6,3215,'2008-05-02',3,101);
INSERT INTO Undergoes VALUES(100000001,2,3215,'2008-05-03',7,101);
INSERT INTO Undergoes VALUES(100000004,1,3217,'2008-05-07',3,102);
INSERT INTO Undergoes VALUES(100000004,5,3217,'2008-05-09',6,NULL);
INSERT INTO Undergoes VALUES(100000001,7,3217,'2008-05-10',7,101);
INSERT INTO Undergoes VALUES(100000004,4,3217,'2008-05-13',3,103);

INSERT INTO Trained_In VALUES(3,1,'2008-01-01','2008-12-31');
INSERT INTO Trained_In VALUES(3,2,'2008-01-01','2008-12-31');
INSERT INTO Trained_In VALUES(3,5,'2008-01-01','2008-12-31');
INSERT INTO Trained_In VALUES(3,6,'2008-01-01','2008-12-31');
INSERT INTO Trained_In VALUES(3,7,'2008-01-01','2008-12-31');
INSERT INTO Trained_In VALUES(6,2,'2008-01-01','2008-12-31');
INSERT INTO Trained_In VALUES(6,5,'2007-01-01','2007-12-31');
INSERT INTO Trained_In VALUES(6,6,'2008-01-01','2008-12-31');
INSERT INTO Trained_In VALUES(7,1,'2008-01-01','2008-12-31');
INSERT INTO Trained_In VALUES(7,2,'2008-01-01','2008-12-31');
INSERT INTO Trained_In VALUES(7,3,'2008-01-01','2008-12-31');
INSERT INTO Trained_In VALUES(7,4,'2008-01-01','2008-12-31');
INSERT INTO Trained_In VALUES(7,5,'2008-01-01','2008-12-31');
INSERT INTO Trained_In VALUES(7,6,'2008-01-01','2008-12-31');
INSERT INTO Trained_In VALUES(7,7,'2008-01-01','2008-12-31');

QUESTION:

Obtain the names of all physicians that have performed a medical procedure they have never been certified to perform.

Click to see solution

SELECT Name 
  FROM Physician 
 WHERE EmployeeID IN 
       (
         SELECT Physician FROM Undergoes U WHERE NOT EXISTS 
            (
               SELECT * FROM Trained_In 
                WHERE Treatment = Procedure 
                  AND Physician = U.Physician
            )
       );

SELECT P.Name FROM 
 Physician AS P,    
 (SELECT Physician, Procedure FROM Undergoes 
    EXCEPT 
    SELECT Physician, Treatment FROM Trained_in) AS Pe
 WHERE P.EmployeeID=Pe.Physician

Same as the previous query, but include the following information in the results: Physician name, name of procedure, date when the procedure was carried out, name of the patient the procedure was carried out on.

Click to see solution

SELECT P.Name AS Physician, Pr.Name AS Procedure, U.Date, Pt.Name AS Patient 
  FROM Physician P, Undergoes U, Patient Pt, Procedure Pr
  WHERE U.Patient = Pt.SSN
    AND U.Procedure = Pr.Code
    AND U.Physician = P.EmployeeID
    AND NOT EXISTS 
              (
                SELECT * FROM Trained_In T
                WHERE T.Treatment = U.Procedure 
                AND T.Physician = U.Physician
              );

SELECT P.Name,Pr.Name,U.Date,Pt.Name FROM 
 Physician AS P,    
 Procedure AS Pr,
 Undergoes AS U,
 Patient AS Pt,
 (SELECT Physician, Procedure FROM Undergoes 
    EXCEPT 
    SELECT Physician, Treatment FROM Trained_in) AS Pe  
 WHERE P.EmployeeID=Pe.Physician AND Pe.Procedure=Pr.Code AND Pe.Physician=U.Physician AND Pe.Procedure=U.Procedure AND U.Patient=Pt.SSN

Obtain the names of all physicians that have performed a medical procedure that they are certified to perform, but such that the procedure was done at a date (Undergoes.Date) after the physician's certification expired (Trained_In.CertificationExpires).

Click to see solution

SELECT Name 
  FROM Physician 
 WHERE EmployeeID IN 
       (
         SELECT Physician FROM Undergoes U 
          WHERE Date > 
               (
                  SELECT CertificationExpires 
                    FROM Trained_In T 
                   WHERE T.Physician = U.Physician 
                     AND T.Treatment = U.Procedure
               )
       );

SELECT P.Name FROM 
 Physician AS P,    
 Trained_In T,
 Undergoes AS U
 WHERE T.Physician=U.Physician AND T.Treatment=U.Procedure AND U.Date>T.CertificationExpires AND P.EmployeeID=U.Physician

Click to see solution

SELECT P.Name AS Physician, Pr.Name AS Procedure, U.Date, Pt.Name AS Patient, T.CertificationExpires
  FROM Physician P, Undergoes U, Patient Pt, Procedure Pr, Trained_In T
  WHERE U.Patient = Pt.SSN
    AND U.Procedure = Pr.Code
    AND U.Physician = P.EmployeeID
    AND Pr.Code = T.Treatment
    AND P.EmployeeID = T.Physician
    AND U.Date > T.CertificationExpires;

Obtain the information for appointments where a patient met with a physician other than his/her primary care physician. Show the following information: Patient name, physician name, nurse name (if any), start and end time of appointment, examination room, and the name of the patient's primary care physician.

Click to see solution

SELECT Pt.Name AS Patient, Ph.Name AS Physician, N.Name AS Nurse, A.Start, A.End, A.ExaminationRoom, PhPCP.Name AS PCP
  FROM Patient Pt, Physician Ph, Physician PhPCP, Appointment A LEFT JOIN Nurse N ON A.PrepNurse = N.EmployeeID
 WHERE A.Patient = Pt.SSN
   AND A.Physician = Ph.EmployeeID
   AND Pt.PCP = PhPCP.EmployeeID
   AND A.Physician <> Pt.PCP;

The Patient field in Undergoes is redundant, since we can obtain it from the Stay table. There are no constraints in force to prevent inconsistencies between these two tables. More specifically, the Undergoes table may include a row where the patient ID does not match the one we would obtain from the Stay table through the Undergoes.Stay foreign key. Select all rows from Undergoes that exhibit this inconsistency.

Click to see solution

SELECT * FROM Undergoes U
 WHERE Patient <> 
   (
     SELECT Patient FROM Stay S
      WHERE U.Stay = S.StayID
   );

Obtain the names of all the nurses who have ever been on call for room 123.

Click to see solution

SELECT N.Name FROM Nurse N
 WHERE EmployeeID IN
   (
     SELECT OC.Nurse FROM On_Call OC, Room R
      WHERE OC.BlockFloor = R.BlockFloor
        AND OC.BlockCode = R.BlockCode
        AND R.Number = 123
   );

The hospital has several examination rooms where appointments take place. Obtain the number of appointments that have taken place in each examination room.
N.b. The solution below fails in MS SQL Server Management Studio, with the following message:

 Msg 306, Level 16, State 2, Line 473
 The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Click to see solution

SELECT ExaminationRoom, COUNT(AppointmentID) AS Number FROM Appointment
GROUP BY ExaminationRoom;

Obtain the names of all patients (also include, for each patient, the name of the patient's primary care physician), such that \emph{all} the following are true:

The patient has been prescribed some medication by his/her primary care physician.
The patient has undergone a procedure with a cost larger that $5,000
The patient has had at least two appointment where the nurse who prepped the appointment was a registered nurse.
The patient's primary care physician is not the head of any department.

Click to see solution

SELECT Pt.Name, PhPCP.Name FROM Patient Pt, Physician PhPCP
 WHERE Pt.PCP = PhPCP.EmployeeID
   AND EXISTS
       (
         SELECT * FROM Prescribes Pr
          WHERE Pr.Patient = Pt.SSN
            AND Pr.Physician = Pt.PCP
       )
   AND EXISTS
       (
         SELECT * FROM Undergoes U, Procedure Pr
          WHERE U.Procedure = Pr.Code
            AND U.Patient = Pt.SSN
            AND Pr.Cost > 5000
       )
   AND 2 <=
       (
         SELECT COUNT(A.AppointmentID) FROM Appointment A, Nurse N
          WHERE A.PrepNurse = N.EmployeeID
            AND N.Registered = 1
       )
   AND NOT Pt.PCP IN
       (
          SELECT Head FROM Department
       );

------------------------------------------------------------------------------------------------------------------------------------------------------------------

MOVIE THEATER

CREATE TABLE Movies ( Code INTEGER PRIMARY KEY NOT NULL,
   Title TEXT NOT NULL,
   Rating TEXT
);

CREATE TABLE MovieTheaters (
   Code INTEGER PRIMARY KEY NOT NULL,
   Name TEXT NOT NULL,
   Movie INTEGER
     CONSTRAINT fk_Movies_Code REFERENCES Movies(Code)
);

INSERT INTO Movies(Code,Title,Rating) VALUES(1,'Citizen Kane','PG');
INSERT INTO Movies(Code,Title,Rating) VALUES(2,'Singin'' in the Rain','G');
INSERT INTO Movies(Code,Title,Rating) VALUES(3,'The Wizard of Oz','G');
INSERT INTO Movies(Code,Title,Rating) VALUES(4,'The Quiet Man',NULL);
INSERT INTO Movies(Code,Title,Rating) VALUES(5,'North by Northwest',NULL);
INSERT INTO Movies(Code,Title,Rating) VALUES(6,'The Last Tango in Paris','NC-17');
INSERT INTO Movies(Code,Title,Rating) VALUES(7,'Some Like it Hot','PG-13');
INSERT INTO Movies(Code,Title,Rating) VALUES(8,'A Night at the Opera',NULL);

INSERT INTO MovieTheaters(Code,Name,Movie) VALUES(1,'Odeon',5);
INSERT INTO MovieTheaters(Code,Name,Movie) VALUES(2,'Imperial',1);
INSERT INTO MovieTheaters(Code,Name,Movie) VALUES(3,'Majestic',NULL);
INSERT INTO MovieTheaters(Code,Name,Movie) VALUES(4,'Royale',6);
INSERT INTO MovieTheaters(Code,Name,Movie) VALUES(5,'Paraiso',3);
INSERT INTO MovieTheaters(Code,Name,Movie) VALUES(6,'Nickelodeon',NULL);

QUESTIONS:

Select the title of all movies.

Click to see solution

 SELECT Title 
   FROM Movies;

Show all the distinct ratings in the database.

Click to see solution

 SELECT DISTINCT Rating 
   FROM Movies;

Show all unrated movies.

Click to see solution

 SELECT * 
   FROM Movies 
  WHERE Rating IS NULL;

Select all movie theaters that are not currently showing a movie.

Click to see solution

 SELECT * 
   FROM MovieTheaters 
  WHERE Movie IS NULL;

Select all data from all movie theaters and, additionally, the data from the movie that is being shown in the theater (if one is being shown).

Click to see solution

 SELECT *
   FROM MovieTheaters LEFT JOIN Movies
   ON MovieTheaters.Movie = Movies.Code;

Select all data from all movies and, if that movie is being shown in a theater, show the data from the theater.

Click to see solution

 SELECT *
   FROM MovieTheaters RIGHT JOIN Movies
   ON MovieTheaters.Movie = Movies.Code;

Show the titles of movies not currently being shown in any theaters.

Click to see solution

 /* With JOIN */
 SELECT Movies.Title
   FROM MovieTheaters RIGHT JOIN Movies
   ON MovieTheaters.Movie = Movies.Code
   WHERE MovieTheaters.Movie IS NULL;
 
 /* With subquery */
 SELECT Title FROM Movies
   WHERE Code NOT IN
   (
     SELECT Movie FROM MovieTheaters
     WHERE Movie IS NOT NULL
   );

Add the unrated movie "One, Two, Three".

Click to see solution

 INSERT INTO Movies(Title,Rating) VALUES('One, Two, Three',NULL);

Set the rating of all unrated movies to "G".

Click to see solution

 UPDATE Movies SET Rating='G' WHERE Rating IS NULL;

Remove movie theaters projecting movies rated "NC-17".

Click to see solution

 DELETE FROM MovieTheaters WHERE Movie IN
   (SELECT Code FROM Movies WHERE Rating = 'NC-17');

-----------------------------------------------------------

-----------------------------------------------------------

COMPUTER STORE DATA BASE

CREATE TABLE Manufacturers (
        Code INTEGER PRIMARY KEY NOT NULL,
        Name TEXT NOT NULL
);

CREATE TABLE Products (
        Code INTEGER PRIMARY KEY NOT NULL,
        Name TEXT NOT NULL ,
        Price REAL NOT NULL ,
        Manufacturer INTEGER NOT NULL
                CONSTRAINT fk_Manufacturers_Code REFERENCES MANUFACTURERS(Code)
);

INSERT INTO Manufacturers(Code,Name) VALUES(1,'Sony');
INSERT INTO Manufacturers(Code,Name) VALUES(2,'Creative Labs');
INSERT INTO Manufacturers(Code,Name) VALUES(3,'Hewlett-Packard');
INSERT INTO Manufacturers(Code,Name) VALUES(4,'Iomega');
INSERT INTO Manufacturers(Code,Name) VALUES(5,'Fujitsu');
INSERT INTO Manufacturers(Code,Name) VALUES(6,'Winchester');

INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(1,'Hard drive',240,5);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(2,'Memory',120,6);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(3,'ZIP drive',150,4);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(4,'Floppy disk',5,6);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(5,'Monitor',240,1);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(6,'DVD drive',180,2);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(7,'CD drive',90,2);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(8,'Printer',270,3);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(9,'Toner cartridge',66,3);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(10,'DVD burner',180,2);

QUESTION AND ANSWER:

Select the names of all the products in the store.

Click to see solution

SELECT Name FROM Products;

Select the names and the prices of all the products in the store.

Click to see solution

SELECT Name, Price FROM Products;

Select the name of the products with a price less than or equal to $200.

Click to see solution

SELECT Name FROM Products WHERE Price <= 200;

Select all the products with a price between $60 and $120.

Click to see solution

 /* With AND */
 SELECT * FROM Products
   WHERE Price >= 60 AND Price <= 120;
 
 /* With BETWEEN */
 SELECT * FROM Products
   WHERE Price BETWEEN 60 AND 120;

Select the name and price in cents (i.e., the price must be multiplied by 100).

Click to see solution

 /* Without AS */
 SELECT Name, Price * 100 FROM Products;
 
 /* With AS */
 SELECT Name, Price * 100 AS PriceCents FROM Products;

Compute the average price of all the products.

Click to see solution

 SELECT AVG(Price) FROM Products;

Compute the average price of all products with manufacturer code equal to 2.

Click to see solution

 SELECT AVG(Price) FROM Products WHERE Manufacturer=2;

Compute the number of products with a price larger than or equal to $180.

Click to see solution

 SELECT COUNT(*) FROM Products WHERE Price >= 180;

Select the name and price of all products with a price larger than or equal to $180, and sort first by price (in descending order), and then by name (in ascending order).

Click to see solution

   SELECT Name, Price 
     FROM Products
    WHERE Price >= 180
 ORDER BY Price DESC, Name;

Select all the data from the products, including all the data for each product's manufacturer.

Click to see solution

 /* Without INNER JOIN */
 SELECT * FROM Products, Manufacturers
   WHERE Products.Manufacturer = Manufacturers.Code;
 
 /* With INNER JOIN */
 SELECT *
   FROM Products INNER JOIN Manufacturers
   ON Products.Manufacturer = Manufacturers.Code;

Select the product name, price, and manufacturer name of all the products.

Click to see solution

 /* Without INNER JOIN */
 SELECT Products.Name, Price, Manufacturers.Name
   FROM Products, Manufacturers
   WHERE Products.Manufacturer = Manufacturers.Code;
 
 /* With INNER JOIN */
 SELECT Products.Name, Price, Manufacturers.Name
   FROM Products INNER JOIN Manufacturers
   ON Products.Manufacturer = Manufacturers.Code;

Select the average price of each manufacturer's products, showing only the manufacturer's code.

Click to see solution

  SELECT AVG(Price), Manufacturer
    FROM Products
GROUP BY Manufacturer;

Select the average price of each manufacturer's products, showing the manufacturer's name.

Click to see solution

 /* Without INNER JOIN */
 SELECT AVG(Price), Manufacturers.Name
   FROM Products, Manufacturers
   WHERE Products.Manufacturer = Manufacturers.Code
   GROUP BY Manufacturers.Name;
 
 /* With INNER JOIN */
 SELECT AVG(Price), Manufacturers.Name
   FROM Products INNER JOIN Manufacturers
   ON Products.Manufacturer = Manufacturers.Code
   GROUP BY Manufacturers.Name;

Select the names of manufacturer whose products have an average price larger than or equal to $150.

Click to see solution

 /* Without INNER JOIN */
 SELECT AVG(Price), Manufacturers.Name
   FROM Products, Manufacturers
   WHERE Products.Manufacturer = Manufacturers.Code
   GROUP BY Manufacturers.Name
   HAVING AVG(Price) >= 150;
 
 /* With INNER JOIN */
 SELECT AVG(Price), Manufacturers.Name
   FROM Products INNER JOIN Manufacturers
   ON Products.Manufacturer = Manufacturers.Code
   GROUP BY Manufacturers.Name
   HAVING AVG(Price) >= 150;

Select the name and price of the cheapest product.

Click to see solution

 SELECT Name, Price
   FROM Products
   WHERE Price = (SELECT MIN(Price) FROM Products);

Select the name of each manufacturer along with the name and price of its most expensive product.

Click to see solution

 /* Without INNER JOIN */
   SELECT A.Name, A.Price, F.Name
   FROM Products A, Manufacturers F
   WHERE A.Manufacturer = F.Code
     AND A.Price =
     (
       SELECT MAX(A.Price)
         FROM Products A
         WHERE A.Manufacturer = F.Code
     );
 
 /* With INNER JOIN */
   SELECT A.Name, A.Price, F.Name
   FROM Products A INNER JOIN Manufacturers F
   ON A.Manufacturer = F.Code
     AND A.Price =
     (
       SELECT MAX(A.Price)
         FROM Products A
         WHERE A.Manufacturer = F.Code
     );

Add a new product: Loudspeakers, $70, manufacturer 2.

Click to see solution

 INSERT INTO Products( Name , Price , Manufacturer)
  VALUES ( 'Loudspeakers' , 70 , 2 );

Update the name of product 8 to "Laser Printer".

Click to see solution

 UPDATE Products
   SET Name = 'Laser Printer'
   WHERE Code = 8;

Apply a 10% discount to all products.

Click to see solution

 UPDATE Products
   SET Price = Price * 0.9;

Apply a 10% discount to all products with a price larger than or equal to $120.

Click to see solution

 UPDATE Products
   SET Price = Price * 0.9
   WHERE Price >= 120;

Training World

Pages

SQL

Case Study

No comments:

Post a Comment

click here to earn

Total Pageviews

Click and enjoy