PepinTEEN 2020 - Atelier SQL - Robert Nițu Teme

PepinTEEN Atelier SQL - Robert Nițu - Teme

Mai jos sunt toate temele pentru atelier.

SQL - Tema #1

CREATE database biblioteca

CREATE TABLE carti (
	_id tinyint unsigned not null auto_increment,
    titlu varchar(32) not null,
    nr_pagini int not null,
    editura varchar(32) not null,
    an_aparitie int not null,
    constraint pk_carti primary key(_id)
);

CREATE TABLE autori (
	_id tinyint unsigned not null auto_increment,
    nume varchar(32) not null,
    prenume varchar(32) not null,
    data_nastere date not null,
    tara varchar(16) not null,
    constraint pk_autori primary key(_id)
);

INSERT INTO carti (titlu, nr_pagini, editura, an_aparitie) VALUES ("Povestea Mea", 624, "Litera", 2018);
INSERT INTO autori (nume, prenume, data_nastere, tara) VALUES ("Michelle",  "Obama", '1967-01-17', "Chicagho");

INSERT INTO carti (titlu, nr_pagini, editura, an_aparitie) VALUES ("Prietena mea geniala", 334, "Pandora M", 2018);
INSERT INTO autori (nume, prenume, data_nastere, tara) VALUES ("Elena", "Ferrante", '1943-10-10', "Italia");

INSERT INTO carti (titlu, nr_pagini, editura, an_aparitie) VALUES ("Invatare. Memorii", 448, "Narator", 2019);
INSERT INTO autori (nume, prenume, data_nastere, tara) VALUES ("Tara", "Westover", '1986-09-27', "S.U.A");

INSERT INTO carti (titlu, nr_pagini, editura, an_aparitie) VALUES ("Foc si Sange", 52, "Nemira", 2014);
INSERT INTO autori (nume, prenume, data_nastere, tara) VALUES ("George", "Martin", '1948-09-20', "S.U.A");

INSERT INTO carti (titlu, nr_pagini, editura, an_aparitie) VALUES ("Idioata", 75, "Curtea Veche", 2016);
INSERT INTO autori (nume, prenume, data_nastere, tara) VALUES ("Elif", "Batunam", '1977-06-07', "S.U.A");

UPDATE autori SET tara = 'S.U.A' WHERE nume = 'Michelle' LIMIT 1;

SELECT * FROM autori;

SELECT * FROM carti WHERE (nr_pagini > 50 AND nr_pagini < 80) ORDER BY nr_pagini ASC, titlu DESC; 
SELECT * FROM autori WHERE (2020 - date_format(data_nastere, "%y") > 20) ORDER BY nume ASC;

SQL - Tema #3

USE northwind;

-- ex) 5
SELECT * FROM employees;
SELECT  EmployeeID, concat(LastName, ' ', FirstName) as NumeIntreg,
		CASE 
			WHEN EmployeeID = 1 THEN 'Level 1'
            WHEN EmployeeID = 2 THEN 'Level 2'
            WHEN EmployeeID = 3 THEN 'Level 3'
		ELSE 'Other Level' END as Status
FROM employees;

-- ex) 6
SELECT * FROM northwind.`order details`;
SELECT  OrderID, ProductID,
		if(UnitPrice >= 0 AND UnitPrice <= 29, "Intervalul 0-29", if(UnitPrice >= 30 AND UnitPrice <= 59, "Intervalul 30-59", "Peste 60")) as Status
FROM orderdetails;

-- ex) 7
SELECT * FROM customers;
SELECT	CustomerID, CompanyName,
		if(isnull(Region), City, Region) as VerificareUnu,
        if(isnull(Region), "No", "Yes") as Completat
FROM customers;

-- ex) 8
SELECT  date_add(curdate(), INTERVAL -1 year) as AcumUnAn,
	    date_format(curdate(), '%m') as Luna,
        date_format(curdate(), '%d') as Zi,
        datediff(curdate(), date_add(curdate(), INTERVAL -1 year)) as DiferentaInZile,
        now() as Acum;
        
-- ex) 9
SELECT * FROM customers;
SELECT  CustomerID, CompanyName,
		INSTR(ContactName, ' ') as CaracterSpatiu_Pozitie,
        UPPER(left(ContactName, INSTR(ContactName, ' '))) as NumeMajuscule_Contact,
        UPPER(right(ContactName, INSTR(ContactName, ' '))) as PrenumeMajuscule_Contact,
        length(ContactName) as LungimeContactName,
        concat(left(ContactName, INSTR(ContactName, ' ')), ',', right(ContactName, INSTR(ContactName, ' '))) as Nume_Despartit
FROM customers;

-- ex) 10
SELECT * FROM orders;
SELECT  OrderID, OrderDate, RequiredDate,
		if(datediff(RequiredDate, OrderDate) > 10, "Termen Depasit", "La timp") as StatusComanda
FROM orders;

-- ex) 11
SELECT  OrderID,
		sum(O.UnitPrice * O.Quantity) as VanzareProdus,
	    sum(P.UnitPrice * P.QuantityPerUnit) as CumparareProdus,
        if(sum(P.UnitPrice * P.QuantityPerUnit) > sum(O.UnitPrice * O.Quantity), 'Profit', 'Pierdere') as Statuus,
        if(abs((sum(P.UnitPrice * P.QuantityPerUnit) - sum(O.UnitPrice * O.Quantity))) < 1950, 'Mic', 'Mare') as StatusAles,
        lower(ProductName) as CategoriiMici
FROM Products as P
inner join orderdetails as O on P.ProductID = O.ProductID GROUP BY P.ProductID;