Database – Experiment two

--1. Simple query
--a) Query the name SNAME of the supplier whose number is S1 and whose CITY is CITY
select SNAME,CITY from S where SNO='S1';

--b) Query the part number in red
select PNO from P where COLOR = 'red';

--c) Query project name JNAME where project location is Tianjin
select JNAME from J where CITY='tianjin';

--d) Query the supplier number of project J1 part P1
select SNO from  SPJ where JNO='J1' and PNO = 'P1';

--2. Query the vendor name and vendor name using lowercase and uppercase letters respectively
-- big -- "small upper(field name)
-- small -- "large Lower(field name)
select SNAME,SNO,Lower(SNO) sno from S;

--3. Enquire all suppliers of engineering parts
select distinct SNO  from SPJ ;

--4. Query the name of the supplier and the city where the supplier is located, and sort the suppliers in the same city in ascending order
-- It doesn't feel right
select s.SNAME,s.CITY from SPJ spj,S s   where   spj.SNO=s.SNO   order by  s.CITY asc,s.SNAME desc;



--5. Query the engineering number of the parts supplied by supplier S1
select JNO from SPJ where SNO='S1' order by JNO asc;


--6. Query the total number of parts
select COUNT(PNO) from SPJ;

--7. Query the part number, part name, and color of all parts starting with "Screw"
select PNO,PNAME,COLOR from P where PNAME like 'screw %';


--8. Query the quantity of part P3 supplied by each supplier
select s.SNAME,COUNT(sj.PNO)  from SPJ sj,S s where PNO='P3' and s.SNO = sj.SNO group by s.SNAME;

--9. Supplier firm SNO for the red parts of Project J1, resulting in no duplicate records
select distinct SNO from SPJ where PNO IN(select PNO from P where COLOR='red')


--10. Numbers of all parts supplied by Shanghai manufacturer
select  distinct PNO from SPJ where SNO IN(select SNO from S where CITY='Shanghai')


--11. Name of the project using parts made in Shanghai
-- Is it better to use IN or equal?
select JNAME from J where JNO IN(
	select JNO from SPJ where SNO IN(
		select SNO from S where CITY='Shanghai'))--12. The engineering number of the parts made in Tianjin is not used
-- Not In? Still exists
select distinct JNO from SPJ where SNO not IN (select SNO from S where CITY='tianjin') 


--13. The Project number of the red parts produced by the Tianjin supplier is not used
select distinct JNO from SPJ where SNO not IN(select SNO from S where CITY='tianjin') 
AND PNO IN(select PNO from P where COLOR='red')


--14. At least the engineering number Jno of all parts supplied by supplier S1 is used
select distinct JNO from SPJ where SNO IN(select SNO from SPJ where SNO='S1') If S1 exists, it will check all the data in the table

select distinct JNO  from SPJ where  SNO IN(select SNO from SPJ where SNO='S1')
Copy the code