PROJEKT
KRIJIMI I NJE BAZE TE DHENASH DHE VEPRIME ME SELECT
USHTRIMET E FAQES 100 TIK 12 IRISOFT
CREATE TABLE Furnitor ( f_id integer primary key,
f_emer varchar (15) NOT NULL,
adresa varchar(50)
);
CREATE TABLE Pjese ( p_id integer primary key,
p_emer varchar (15) NOT NULL,
ngjyra varchar(20)
);
CREATE TABLE Katalogu ( f_id integer Foreign key references Furnitor (f_id),
p_id integer Foreign key references Pjese (p_id),
kosto integer NOT NULL,
Primary key ( f_id, p_id)
);
3. Afisho te gjitha detajet e katalogut:
Select * From Katalogu;
4. Afisho kostot e ndryshme te pjeseve
Select Distinct Kosto From Katalogu;
5. Afishoni detajet e katalogut me kosto me te medha se 500
Select * from Katalogu where kosto >500;
6. Afishoni detajet e pjeseve me ngjyre jeshile ose te verdhe
Select * from Pjese where ngjyra IN ( ‘jeshile’ , ‘ verdhe’);
7. Afishoni emrat e furnitoreve qe permbajne te pakten germen S.
SELECT f_emri FROM Furnitor
WHERE f_emri LIKE '%s%';
8. afishoni emrat e pjeseve te cilave u njihet ngjyra
SELECT p_emri FROM pjese WHERE Ngjyra IS NOT
NULL;
9. Afishoni detajet e pjeseve sipas emrave ne rendin rrites
SELECT * FROM pjese ORDER BY p_emri ASC
10. Gjeni emrat e furnitoreve qe furnizojne pjese te kuqe
SELECT DISTINCT f_emer
FROM Furnitor AS f
JOIN Katalogu As k ON f.f_id = k.f_id
JOIN Pjese As p ON k.p_id = p.p_id
WHERE ngjyra = 'kuqe';
11. Afishoni kodet e furnitoreve qe shesin pjese qe kushtojne me shume se 100
SELECT DISTINCT f.f_id
FROM Furnitor As f
JOIN Katalogu As k ON f.f_id = k.f_id
WHERE [Link] > 100;
12. Gjeni kodet e furnitoreve qe furnizojne pjese te kuqe ose jeshile
SELECT DISTINCT f.f_id
FROM Furnitor As f
JOIN Katalogu As k ON f.f_id = k.f_id
JOIN Pjese As p ON k.p_id = p.p_id
WHERE [Link] IN ('kuqe', 'jeshile');
Ose
SELECT DISTINCT f.f_id
FROM Furnitor AS f
JOIN Katalogu As k ON f.f_id = k.f_id
JOIN Pjese As p ON k.p_id = p.p_id
WHERE [Link] = 'kuqe' OR [Link] = 'jeshile';
13. Gjeni kodet e furnitoreve te cilet furnizojne pjese te kuqe dhe jetojne ne Lushnje.
SELECT DISTINCT f_id
FROM Furnitor
JOIN Katalogu ON f_id = f_id
JOIN Pjese ON p_id = p_id
WHERE ngjyra = 'kuqe' AND adresa = 'Lushnje';
14. Gjeni sa furnitore furnizojne pjese te kuqe.
SELECT COUNT (DISTINCT f_id) AS numri_furnitoreve
FROM Furnitor
JOIN Katalogu ON f_id = f_id
JOIN Pjese ON p_id = p_id
WHERE ngjyra = 'kuqe';
15. Gjeni kodet e pjeseve qe ofron furnitori me emrin Liam.
SELECT DISTINCT p_id
FROM Furnitor
JOIN Katalogu ON f_id = f_id
WHERE f_emer = 'Liam';
16. Afishon kodet e furnitoreve qe shesin pjese te kuqe qe kushtojne me shume se 100 leke. Te
mos kthehet duplikata.
SELECT DISTINCT f.f_id
FROM Furnitor f
JOIN Katalogu k ON f.f_id = k.f_id
JOIN Pjese p ON k.p_id = p.p_id
WHERE [Link] = 'kuqe' AND [Link] > 100;
17. Afishoni per cdo furnitor kodin dhe sasine e pjeseve qe shet ai te cilat kushtojne me shume se
100.
SELECT f.f_id, COUNT(k.p_id) AS sasia
FROM Furnitor f
JOIN Katalogu k ON f.f_id = k.f_id
JOIN Pjese p ON k.p_id = p.p_id
WHERE [Link] > 100
GROUP BY f.f_id;
