SQL medium 1

DELETE

DELETE FROM Elevi
WHERE Prenume='Mihai' AND Scoala='gen.Radulescu';

realizati o stergere de inregistrare asemanatoare cu cea de mai sus si in tabla Angajati

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

SELECT LIMIT

SELECT * FROM Elevi ORDER BY NrCrtID LIMIT 2;


realizati o selectie asemanatoare cu cea de mai sus si in tabla Angajati

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

CHEI STRAINE

CREATE TABLE clienti (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    nume CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE tricouri (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    tip ENUM('simplu', 'polo', 'de dama') NOT NULL,
    culoare ENUM('rosu', 'albastru', 'portocaliu', 'alb', 'negru') NOT NULL,
    proprietar SMALLINT UNSIGNED NOT NULL REFERENCES clienti(id),
    PRIMARY KEY (id)
);

INSERT INTO clienti VALUES (NULL, 'Vasilescu Ion');

SELECT @last := LAST_INSERT_ID();

INSERT INTO tricouri VALUES
(NULL, 'polo', 'albastru', @last),
(NULL, 'de dama', 'alb', @last),
(NULL, 'simplu', 'albastru', @last);

INSERT INTO clienti VALUES (NULL, 'Lilliana Franculescu');

SELECT @last := LAST_INSERT_ID();

INSERT INTO tricouri VALUES
(NULL, 'de dama', 'portocaliu', @last),
(NULL, 'polo', 'rosu', @last),
(NULL, 'de dama', 'albastru', @last),
(NULL, 'simplu', 'alb', @last);

SELECT * FROM clienti;
+----+---------------------+
| id | nume                |
+----+---------------------+
|  1 | Vasilescu Ion       |
|  2 | Lilliana Franculescu|
+----+---------------------+

SELECT * FROM tricouri;
+----+---------+------------+-------
| id | tip     | culoare    | proprietar
+----+---------+------------+---------
|  1 | polo    | albastru   |     1
|  2 | dress   | alb        |     1
|  3 | simplu  | albastru   |     1
|  4 | de dama | portocaliu |     2
|  5 | polo    | rosu       |     2
|  6 | de dama | albastru   |     2
|  7 | simplu  | alb        |     2
+----+---------+--------+------------


SELECT s.* FROM clienti p INNER JOIN tricouri s
   ON s.proprietar = p.id
 WHERE p.nume LIKE 'Lilliana%'
   AND s.culoare <> 'alb';

+----+---------+------------+----------
| id | tip     | culoare    | proprietar
+----+---------+------------+---------
|  4 | de dama | portocaliu |     2 |
|  5 | polo    | rosu       |     2 |
|  6 | de dama | albastru   |     2 |
+----+-------+--------+-------------+

Realizati inca un program asemanator cu 2 tabele: angajati2 si obiecte, unde la obiecte vor fi laptop, veioza, dosare, pix, imprimanta fiecare din cei 3 angajati cu cate 3 obiecte diferite.