DROP TABLE IF EXISTS cashier;
DROP TABLE IF EXISTS customer;
DROP TABLE IF EXISTS sale;
DROP TABLE IF EXISTS product;
DROP TABLE IF EXISTS product_sale;
CREATE TABLE cashier (
cid INTEGER,
name STRING,
age INTEGER,
wage REAL,
PRIMARY KEY(cid)
);
CREATE TABLE customer (
cuid INTEGER,
name STRING,
age INTEGER,
avg_spending REAL,
PRIMARY KEY(cuid)
);
CREATE TABLE product (
pid INTEGER,
name STRING,
expiry_date DATE,
minimum_age INTEGER,
price REAL,
PRIMARY KEY(pid)
);
CREATE TABLE sale (
sid INTEGER,
cid INTEGER,
cuid INTEGER,
time_secs INTEGER,
transaction_day DATE,
PRIMARY KEY(sid),
FOREIGN KEY(cid) REFERENCES cashier,
FOREIGN KEY(cuid) REFERENCES customer
);
CREATE TABLE product_sale (
sid INTEGER,
pid INTEGER,
amount INTEGER NOT NULL,
PRIMARY KEY(sid, pid),
FOREIGN KEY (sid) REFERENCES sale,
FOREIGN KEY (pid) REFERENCES product
);
INSERT INTO cashier VALUES(1, "Bartjan Wiering", 25, 13);
INSERT INTO cashier VALUES(2, "Lyon Meijer", 55, 15);
INSERT INTO cashier VALUES(3, "Desley Lambert", 13, 4);
INSERT INTO cashier VALUES(4, "Willem ten Graaf", 42, 40);
INSERT INTO cashier VALUES(5, "Corneel Bijl", 61, 25);
INSERT INTO cashier VALUES(6, "Ayla Loef", 26, 25);
INSERT INTO cashier VALUES(7, "Erben Spithoven", 55, 21);
INSERT INTO cashier VALUES(8, "Nadir Wesdorp", 33, 2);
INSERT INTO cashier VALUES(9, "Deni Phan", 35, 35);
INSERT INTO customer VALUES(1, "Erik Hollander", 27, 15);
INSERT INTO customer VALUES(2, "Lourens te Beest", 4, 3);
INSERT INTO customer VALUES(3, "Goedr Harig", 17, 24);
INSERT INTO customer VALUES(4, "Henk Krom", 65, 24);
INSERT INTO customer VALUES(5, "Sean Eeyore", 72, 70);
INSERT INTO customer VALUES(6, "Tim Ager", 40, 80);
INSERT INTO customer VALUES(7, "Greg Gregson", 84, 12);
INSERT INTO customer VALUES(8, "Ron Ager", 42, 82);
INSERT INTO customer VALUES(9, "Bob Krom", 66, 4);
INSERT INTO product VALUES(1, "spaghetti", "2019-03-03", 0, 0.60);
INSERT INTO product VALUES(2, "tomaat", "2019-03-03", 0, 1.20);
INSERT INTO product VALUES(3, "brood", "2019-03-03", 0, 0.90);
INSERT INTO product VALUES(4, "melk", "2019-03-04", 0, 2.50);
INSERT INTO product VALUES(5, "bier", "2019-02-20", 18, 15);
INSERT INTO product VALUES(6, "energy drink", "2019-02-03", 14, 3.40);
INSERT INTO product VALUES(7, "wijn", "2019-02-06", 18, 12);
INSERT INTO product VALUES(8, "jam", "2019-03-01", 0, 4.50);
INSERT INTO product VALUES(9, "pizza", "2019-03-01", 0, 3.40);
INSERT INTO product VALUES(10, "tomaat", "2019-03-03", 0, 2.10);
INSERT INTO product VALUES(11, "melk", "2019-03-07", 0, 2.54);
INSERT INTO sale VALUES(1, 1, 3, 130, "2019-03-04");
INSERT INTO sale VALUES(2, 2, 5, 130, "2019-03-01");
INSERT INTO sale VALUES(3, 4, 4, 130, "2019-02-15");
INSERT INTO sale VALUES(4, 3, 6, 130, "2019-02-27");
INSERT INTO sale VALUES(5, 5, 7, 130, "2019-03-02");
INSERT INTO sale VALUES(6, 1, 2, 130, "2019-03-02");
INSERT INTO sale VALUES(7, 4, 1, 130, "2019-03-02");
INSERT INTO sale VALUES(8, 5, 8, 130, "2019-03-02");
INSERT INTO sale VALUES(9, 2, 9, 130, "2019-03-02");
INSERT INTO sale VALUES(10, 5, 3, 130, "2019-03-02");
INSERT INTO sale VALUES(11, 3, 5, 130, "2019-03-02");
INSERT INTO sale VALUES(12, 5, 7, 130, "2019-03-02");
INSERT INTO sale VALUES(13, 7, 3, 130, "2019-03-02");
INSERT INTO sale VALUES(14, 5, 7, 130, "2019-03-02");
INSERT INTO sale VALUES(15, 4, 9, 130, "2019-03-02");
INSERT INTO sale VALUES(16, 3, 8, 130, "2019-03-02");
INSERT INTO sale VALUES(17, 1, 1, 130, "2019-03-02");
INSERT INTO sale VALUES(18, 4, 3, 130, "2019-03-02");
INSERT INTO sale VALUES(19, 3, 2, 130, "2019-03-02");
INSERT INTO sale VALUES(20, 8, 9, 130, "2019-03-02");
INSERT INTO product_sale VALUES(1, 2, 1);
INSERT INTO product_sale VALUES(1, 1, 2);
INSERT INTO product_sale VALUES(2, 3, 1);
INSERT INTO product_sale VALUES(3, 4, 1);
INSERT INTO product_sale VALUES(4, 2, 1);
INSERT INTO product_sale VALUES(4, 5, 3);
INSERT INTO product_sale VALUES(5, 6, 1);
INSERT INTO product_sale VALUES(6, 4, 3);
INSERT INTO product_sale VALUES(6, 6, 3);
INSERT INTO product_sale VALUES(7, 8, 1);
INSERT INTO product_sale VALUES(8, 9, 1);
INSERT INTO product_sale VALUES(9, 1, 2);
INSERT INTO product_sale VALUES(10, 1, 1);
INSERT INTO product_sale VALUES(10, 3, 3);
INSERT INTO product_sale VALUES(10, 5, 1);
INSERT INTO product_sale VALUES(11, 2, 1);
INSERT INTO product_sale VALUES(11, 3, 2);
INSERT INTO product_sale VALUES(12, 9, 1);
INSERT INTO product_sale VALUES(13, 4, 1);
INSERT INTO product_sale VALUES(14, 8, 3);
INSERT INTO product_sale VALUES(15, 7, 1);
INSERT INTO product_sale VALUES(16, 4, 1);
INSERT INTO product_sale VALUES(17, 6, 1);
INSERT INTO product_sale VALUES(18, 2, 1);
INSERT INTO product_sale VALUES(18, 4, 1);
INSERT INTO product_sale VALUES(18, 3, 2);
INSERT INTO product_sale VALUES(18, 5, 1);
INSERT INTO product_sale VALUES(19, 3, 1);
INSERT INTO product_sale VALUES(20, 2, 3);
INSERT INTO product_sale VALUES(20, 3, 1);
INSERT INTO product_sale VALUES(20, 4, 4);
INSERT INTO product_sale VALUES(20, 9, 1);
INSERT INTO product_sale VALUES(20, 8, 2);
SELECT ' ';
SELECT '3.1';
SELECT ' ';
SELECT cuid from customer C where C.age <18 or C.age>65;
SELECT ' ';
SELECT '3.2';
SELECT ' ';
SELECT name from cashier CA where CA.cid in (select S.cid from sale S, customer C, product P where C.age <P.minimum_age);
SELECT ' ';
SELECT '3.3';
SELECT ' ';
SELECT C.name from customer C where C.cuid in (select S.cuid from sale S, product_sale PS, product P where PS.sid = S.sid and PS.pid = P.pid and P.expiry_date < S.transaction_day);
-- create table
CREATE TABLE BOOKS(
Isbn integer PRIMARY KEY,
Title text,
Author text
);
CREATE TABLE TAGS(
TagId integer PRIMARY KEY,
TagName text
);
CREATE TABLE BOOKS_TAGS(
Isbn integer ,
TagId integer,
PRIMARY KEY (Isbn, TagId),
FOREIGN KEY (Isbn) REFERENCES BOOKS (Isbn),
FOREIGN KEY (TagId) REFERENCES TAGS (TagId)
);
-- insert tags
INSERT INTO TAGS VALUES(1,'kid');
INSERT INTO TAGS VALUES(2,'adult');
INSERT INTO TAGS VALUES(3,'teen');
INSERT INTO TAGS VALUES(4,'fun');
-- insert books
INSERT INTO BOOKS VALUES(1,'whatever','joker');
INSERT INTO BOOKS VALUES(2,'bs','joker');
INSERT INTO BOOKS VALUES(3,'gg', 'tt');
-- tag books
INSERT INTO BOOKS_TAGS VALUES(1,1);
INSERT INTO BOOKS_TAGS VALUES(1,2);
INSERT INTO BOOKS_TAGS VALUES(2,1);
INSERT INTO BOOKS_TAGS VALUES(2,4);
INSERT INTO BOOKS_TAGS VALUES(3,1);
INSERT INTO BOOKS_TAGS VALUES(3,3);
INSERT INTO BOOKS_TAGS VALUES(3,2);
-- print table
SELECT * FROM TAGS;
SELECT '';
SELECT * FROM BOOKS;
SELECT '';
SELECT * FROM BOOKS_TAGS;
SELECT '';
-- search books with tags
SELECT TagName, Title , Author
FROM BOOKS JOIN
(BOOKS_TAGS JOIN TAGS ON BOOKS_TAGS.TagId = TAGS.TagId)
ON BOOKS.Isbn = BOOKS_TAGS.Isbn
WHERE TagName = 'kid';
PRAGMA foreign_keys = ON;
/* J'active le foreign_keys pour avoir une bonne intégritée de la base de donnée */
/*Je ne met pas d'auto-incrémente car celui-ci risque d'enfreindre les performances de la base*/
create table Energie
(id_energie integer PRIMARY KEY,
nom_E varchar(30) NOT NULL,
UNIQUE(nom_E));
create table Boite
(id_boite integer PRIMARY KEY,
nom_B varchar(30) NOT NULL,
UNIQUE(nom_B));
create table Categorie
(id_categorie integer PRIMARY KEY,
nom_C varchar(30) NOT NULL,
UNIQUE(nom_C));
create table Ville
(id_ville integer PRIMARY KEY,
nom_V varchar(30) NOT NULL,
UNIQUE(nom_V));
create table Statut
(id_statut integer PRIMARY KEY,
nom_S varchar(30) NOT NULL,
frais_de_structure integer NOT NULL,
pourcentage_de_remise float NOT NULL,
description varchar(100) NOT NULL,
CHECK ((frais_de_structure BETWEEN 0 and 10) AND (pourcentage_de_remise BETWEEN 0 and 20)),
UNIQUE(nom_S));
create table Client
(id_client integer PRIMARY KEY,
id_statut_R integer NOT NULL,
id_statut_P integer NOT NULL,
prenom varchar(30) NOT NULL,
nom_Cl varchar(30) NOT NULL,
age integer NOT NULL,
id_ville integer NOT NULL,
nombre_de_voiture integer NOT NULL,
point_de_fidelite integer NOT NULL,
CHECK ((age BETWEEN 18 AND 200) AND nombre_de_voiture >= 0 AND point_de_fidelite >= 0),
CHECK((length(prenom) >= 2) and (length(nom_Cl) >= 2)),
UNIQUE(prenom, nom_Cl),
FOREIGN KEY(id_ville) REFERENCES Ville(id_ville),
FOREIGN KEY(id_statut_R) REFERENCES Statut(id_statut),
FOREIGN KEY(id_statut_P) REFERENCES Statut(id_statut));
create table Vehicule
(id_vehicule integer PRIMARY KEY,
immatriculation varchar(10) NOT NULL,
modele varchar(30) NOT NULL,
id_energie integer NOT NULL,
id_boite integer NOT NULL,
id_categorie integer NOT NULL,
id_ville integer NOT NULL,
tarif_horaire float NOT NULL,
id_client_V integer NOT NULL,
CHECK (length(immatriculation) >= 3 and length(modele) >= 2 and tarif_horaire BETWEEN 12 and 1500),
UNIQUE (immatriculation),
FOREIGN KEY(id_energie) REFERENCES Energie(id_energie),
FOREIGN KEY(id_boite) REFERENCES Boite(id_boite),
FOREIGN KEY(id_categorie) REFERENCES Categorie(id_categorie),
FOREIGN KEY(id_ville) REFERENCES Ville(id_ville),
FOREIGN KEY(id_client_V) REFERENCES Client(id_client));
create table Reserver
(id_reservation integer PRIMARY KEY,
id_client_R integer NOT NULL,
id_vehicule_R integer NOT NULL,
debut_R Date NOT NULL,
fin_R Date NOT NULL,
prix float NOT NULL,
validation boolean NOT NULL,
/* Toutes ces conditions sur les dates vont permettre de conserver une disponibilitée de l'information et de conserver l'intégritée de la base*/
/*On réserve à l'avance de 1 heure pour une duree miminimum de 1 heure*/
CHECK (prix>=12),
/* je retire cette condition car l'application sera une demonstration et non une situation réel
CHECK (DATETIME(debut_R)>=DATETIME('now','+1 hour')), */
CHECK (DATETIME(debut_R)<=DATETIME(fin_R,'-1 hour')),
CHECK ((validation==0 AND (DATETIME(fin_R)>DATETIME('now'))) OR ((validation==0 OR validation==1) AND (DATETIME(fin_R)<=DATETIME('now')))),
CHECK (debut_R==strftime('%Y-%m-%d %H:%M', debut_R)),
CHECK (fin_R==strftime('%Y-%m-%d %H:%M', fin_R)),
CHECK (validation==0 AND (DATETIME(fin_R)>DATETIME('now'))),
/*On ne peut valider une reservation tant que la date de celui-çi n'est pas passé*/
UNIQUE(id_vehicule_R, debut_R),
UNIQUE(id_vehicule_R, fin_R),
FOREIGN KEY(id_vehicule_R) REFERENCES Vehicule(id_vehicule),
FOREIGN KEY(id_client_R) REFERENCES Client(id_client));
/*Apres plusieurs recherche il n'y a pas en sqlite des fonctions permettant de comparer des dates*/
create table Bailler
(id_baille integer PRIMARY KEY,
id_client_B integer NOT NULL,
id_vehicule_B integer NOT NULL,
debut_B Date NOT NULL,
fin_B Date NOT NULL,
/*
CHECK (Date(debut_B)>=Date(strftime('%Y-%m-%d %H-%M','now','+3 hour'))),
*/
/* Toutes ces conditions sur les dates vont permettre de conserver une disponibilitée de l'information et de conserver l'intégritée de la base*/
/*En l'occurence ici on pourra ajouter un baille d'une duree minimum de 3 heures sans avoir de conflit sur les autres bailles*/
/* CHECK (DATETIME(debut_B)>=DATETIME('now')), je retire cette condition car l'application sera une demonstration et non une situation réel */
CHECK (DATETIME(debut_B)<=DATETIME(fin_B,'-2 hour')),
CHECK (debut_B==strftime('%Y-%m-%d %H:%M', debut_B)),
CHECK (fin_B==strftime('%Y-%m-%d %H:%M', fin_B)),
UNIQUE(id_vehicule_B, debut_B),
UNIQUE(id_vehicule_B, fin_B),
FOREIGN KEY(id_vehicule_B) REFERENCES Vehicule(id_vehicule)
FOREIGN KEY(id_client_B) REFERENCES Client(id_client));
/*L'ensemble des trigger permettent de gerer l'incrémentation du nombre de voiture mais aussi d'actualiser le statut professionnel et utilisateur
*/
create trigger trigger_points_de_fideliteR
AFTER update on Reserver
Begin
Update Client SET point_de_fidelite=point_de_fidelite+1
WHERE id_client==NEW.id_client_R and NEW.validation==1;
End;
create trigger trigger_points_de_fideliteB
AFTER insert on Bailler
Begin
Update Client SET point_de_fidelite=point_de_fidelite+1
WHERE id_client==NEW.id_client_B;
End;
create trigger trigger_nombre_de_voiture
AFTER insert on Vehicule
Begin
Update Client SET nombre_de_voiture=nombre_de_voiture+1
WHERE id_client==NEW.id_client_V;
End;
create trigger statut_client
AFTER update on Client
Begin
Update Client SET id_statut_P=3
WHERE (id_client==NEW.id_client and (nombre_de_voiture BETWEEN 3 and 10) and (id_statut_P==1) and (point_de_fidelite BETWEEN 10 and 20));
Update Client SET id_statut_P=5
WHERE (id_client==NEW.id_client and (nombre_de_voiture>10 and point_de_fidelite>20));
Update Client SET id_statut_R=2
WHERE (id_client==NEW.id_client and (id_statut_R==1) and (point_de_fidelite BETWEEN 10 and 20));
Update Client SET id_statut_R=4
WHERE id_client==NEW.id_client and point_de_fidelite>20;
End;
CREATE TRIGGER detection_conflit_baille
BEFORE INSERT ON Bailler
BEGIN
SELECT CASE WHEN EXISTS(Select id_baille from Bailler where id_vehicule_B==NEW.id_vehicule_B AND ((debut_B < NEW.debut_B and NEW.debut_B < fin_B) or (debut_B < NEW.fin_B and NEW.fin_B < fin_B) or ((NEW.debut_B < debut_B) and (NEW.fin_B > fin_B)))) THEN RAISE (ABORT,'Les Dates saisies ne peuvent être ajouté, il y a des conflits avec d autres bailles pour le même véhicule')
END;
END;
CREATE TRIGGER detection_conflit_reservation
BEFORE INSERT ON Reserver
BEGIN
SELECT CASE WHEN NOT EXISTS(Select id_baille from Bailler where id_vehicule_B==NEW.id_vehicule_R AND ((NEW.debut_R BETWEEN debut_B and fin_B) and (NEW.fin_R BETWEEN debut_B and fin_B))) THEN RAISE (ABORT,'Les Dates saisies ne peuvent être ajouté, il y a pas de véhicule a bayer durant cette période')
END;
SELECT CASE WHEN EXISTS(Select id_reservation from Reserver where id_vehicule_R==NEW.id_vehicule_R AND (((DATETIME(debut_R,'-1 hour') < DATETIME(NEW.debut_R)) and (DATETIME(NEW.debut_R) < DATETIME(fin_R,'+1 hour'))) or ((DATETIME(debut_R,'-1 hour') < DATETIME(NEW.fin_R)) and (DATETIME(NEW.fin_R) < DATETIME(fin_R,'+2 hour'))) or ((NEW.debut_R<DATETIME(debut_R,'-1 hour')) and (NEW.fin_R>DATETIME(fin_R,'+1 hour'))))) THEN RAISE (ABORT,'Les Dates saisies ne peuvent être ajouté, il y a des conflits avec d autres réservations pour le même véhicule')
END;
END;
create TRIGGER calcul_prix
AFTER INSERT on Reserver
BEGIN
UPDATE Reserver SET prix=Round((select (JULIANDAY(fin_R)-JULIANDAY(debut_R))*24*pourcentage_de_remise*tarif_horaire from (Reserver NATURAL JOIN (select id_client as id_client_R, id_statut_R from Client) NATURAL JOIN (select id_vehicule as id_vehicule_R, tarif_horaire from Vehicule) NATURAL JOIN (select id_statut as id_statut_R, pourcentage_de_remise from Statut)) WHERE id_reservation==NEW.id_reservation),2)
WHERE id_reservation=NEW.id_reservation;
END;
CREATE VIEW afficher_les_voiture AS
SELECT *
FROM (Vehicule NATURAL JOIN Energie NATURAL JOIN Ville NATURAL JOIN Boite NATURAL JOIN Categorie);
CREATE VIEW afficher_les_clients AS
SELECT *
FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM Client NATURAL JOIN Ville) NATURAL JOIN (SELECT id_statut as id_statut_R, nom_S as nom_SR, description as dsr FROM Statut)) NATURAL JOIN (SELECT * FROM (SELECT * FROM (SELECT * FROM Client NATURAL JOIN Ville) NATURAL JOIN (SELECT id_statut as id_statut_P, nom_S as nom_SP, description as dsp FROM Statut))));
CREATE VIEW afficher_les_cliens_reservation_non_valide AS
SELECT *
FROM afficher_les_clients NATURAL JOIN Reserver WHERE (validation==0 AND id_client==id_client_R);
insert into Energie values(1, 'Diesel');
insert into Energie values(2, 'Essence');
insert into Energie values(3, 'Flexfuel');
insert into Energie values(4, 'Gpl');
insert into Energie values(5, 'Gpl Essence');
insert into Energie values(6, 'Electrique');
insert into Energie values(7, 'Hybride');
insert into Boite values(1,'Manuel');
insert into Boite values(2,'Automatique');
insert into Boite values(3,'Semi-Automatique');
insert into Boite values(4,'Pilotée');
insert into Categorie values(1,'Mini citadines');
insert into Categorie values(2,'Citadines');
insert into Categorie values(3,'Berlines');
insert into Categorie values(4,'Monospaces');
insert into Categorie values(5,'Cabriolets');
insert into Categorie values(6,'SUV');
insert into Categorie values(7,'Sport');
insert into Categorie values(8,'Super car');
insert into Ville values(1,'Lens');
insert into Ville values(2,'Lille');
insert into Ville values(3,'Douai');
insert into Ville values(4,'Nice');
insert into Ville values(5,'Marseille');
insert into Ville values(6,'Nantes');
insert into Ville values(7,'Toulouse');
insert into Ville values(8,'Montpellier');
insert into Ville values(9,'Strasbourg');
insert into Ville values(10,'Bordeaux');
insert into Ville values(11,'Lyon');
insert into Statut values(1,"Basique",8,1,"Le statut Basique ne donne aucun privilège au client celui-ci doit payer 8 euros lors du baille d'un véhicule");
insert into Statut values(2,"Premium",8,0.9,"Le statut premium permet au client de recevoir une réduction de 10% lors de chaque réservation par ailleurs celui-ci doit payer 8 euros lors du baille d'un véhicule");
insert into Statut values(3,"Pro",6,1,"Le statut Pro permet au client celui-ci de ne payer que 6 euros lors du baille d'un véhicule");
insert into Statut values(4,"Gold",8,0.8,"Le statut premium permet au client de recevoir une réduction de 20% lors de chaque réservation par ailleurs celui-ci doit payer 8 euros lors du baille d'un véhicule");
insert into Statut values(5,"Proplus",4,1,"Le statut Proplus permet au client celui-ci de ne payer que 4 euros lors du baille d'un véhicule");
insert into Client values(1,1,1,"Paul","Nelson",20,1,1,0);
insert into Client values(2,1,1,"Yassin","Morad",21,1,0,0);
insert into Client values(3,1,1,"Francois","Deroux",25,3,0,0);
insert into Client values(4,1,1,"Lockman","Zioueche",19,2,1,0);
insert into Client values(5,1,1,"Jawzi","Fantrossi",30,2,1,0);
insert into Client values(6,1,1,"Neil","Amstrong",50,3,0,0);
insert into Client values(7,1,1,"Charlie","Delvinem",48,1,0,0);
insert into Client values(8,1,1,"Alphonse","Demouveau",38,2,0,0);
insert into Client values(9,1,1,"John","Daverland",60,3,0,0);
insert into Client values(10,1,1,"Emile","Mackenzi",55,4,1,0);
insert into Client values(11,1,1,"John","Yacob",70,5,0,0);
insert into Client values(12,1,1,"Hugo","Sarman",19,6,0,0);
insert into Client values(13,1,1,"Kevin","Bounga",25,7,1,0);
insert into Client values(14,1,1,"Cedric","Dujardin",39,8,0,0);
insert into Client values(15,1,1,"Zakary","Refouch",47,9,1,0);
insert into Client values(16,1,1,"Asslan","Sarman",24,10,1,0);
insert into Client values(17,1,1,"Ivor","Toupan",43,4,0,0);
insert into Vehicule values(1,"immatriculation1","peugeot 107",1,1,1,1,15,1);
insert into Vehicule values(2,"immatriculation2","yaris 2",2,2,2,2,20,2);
insert into Vehicule values(3,"immatriculation3","toyota yaris 1",3,3,2,3,21,3);
insert into Vehicule values(4,"immatriculation4","opel yaris",4,2,2,2,18,4);
insert into Vehicule values(5,"immatriculation5","audi a3",5,2,2,7,25,5);
insert into Vehicule values(6,"immatriculation6","volkswagen polo",1,1,2,1,25,6);
insert into Vehicule values(7,"immatriculation7","Land Rover",1,1,6,3,28,7);
insert into Vehicule values(8,"immatriculation8","Renault zoe",6,2,2,6,28,8);
insert into Vehicule values(9,"immatriculation9","Renault clio",1,1,2,4,28,9);
insert into Vehicule values(10,"immatriculation10","Tesla Model S",6,2,3,6,28,10);
insert into Vehicule values(11,"immatriculation11","Renault kadjar",2,1,6,6,28,11);
insert into Vehicule values(12,"immatriculation12","Honda civic",7,3,7,6,28,12);
insert into Vehicule values(13,"immatriculation13","Honda jazz",7,1,2,6,25,13);
insert into Vehicule values(14,"immatriculation14","Honda Accord",1,1,3,4,28,14);
insert into Vehicule values(15,"immatriculation15","Nissan micra",1,1,1,3,28,15);
insert into Vehicule values(16,"immatriculation16","Nissan almera",1,1,2,2,28,16);
insert into Vehicule values(17,"immatriculation17","volkswagen E-golf",6,2,2,1,28,17);
insert into Vehicule values(18,"immatriculation18","volkswagen scirocco",1,1,7,1,28,16);
insert into Vehicule values(19,"immatriculation19","Hyundai coupe",2,1,7,2,28,2);
insert into Vehicule values(20,"immatriculation20","Hyundai Getz",1,1,2,3,28,1);
insert into Vehicule values(21,"immatriculation21","volkswagen VR6",2,1,7,4,28,9);
insert into Vehicule values(22,"immatriculation22","Honda NSX",2,3,7,5,28,4);
insert into Vehicule values(23,"immatriculation23","Kia rio",1,1,6,6,17,13);
insert into Vehicule values(24,"immatriculation24","Jeep wrangler",1,1,6,7,28,17);
insert into Vehicule values(25,"immatriculation25","Audi R8 V10",2,3,7,10,28,13);
insert into Vehicule values(26,"immatriculation26","Lamborghini huracan spyder",2,3,8,10,760,11);
insert into Vehicule values(27,"immatriculation27","ferrari f8 tributo",2,3,8,10,800,3);
insert into Vehicule values(28,"immatriculation28","nissan gtr nismo",2,3,8,11,630,7);
insert into Bailler values(1,1,1,"2019-04-17 11:00","2020-04-17 11:00");
insert into Bailler values(2,2,2,"2019-04-17 11:00","2020-04-17 11:00");
insert into Bailler values(3,3,3,"2019-04-17 11:00","2020-04-17 11:00");
insert into Bailler values(4,4,4,"2019-04-17 11:00","2020-04-17 11:00");
insert into Bailler values(5,5,5,"2019-04-17 11:00","2020-04-17 11:00");
insert into Bailler values(6,6,6,"2019-04-17 11:00","2020-04-17 11:00");
insert into Bailler values(7,7,7,"2019-04-17 11:00","2020-04-17 11:00");
insert into Bailler values(8,8,8,"2019-04-17 11:00","2020-04-17 11:00");
insert into Bailler values(9,9,9,"2019-04-17 11:00","2020-04-17 11:00");
insert into Bailler values(10,10,10,"2019-04-17 11:00","2020-04-17 11:00");
insert into Bailler values(11,11,11,"2019-04-17 11:00","2020-04-17 11:00");
insert into Bailler values(12,12,12,"2019-04-17 11:00","2020-04-17 11:00");
insert into Bailler values(13,13,13,"2019-04-17 11:00","2020-04-17 11:00");
insert into Bailler values(14,14,14,"2019-04-17 11:00","2020-04-17 11:00");
insert into Bailler values(15,15,15,"2019-04-17 11:00","2020-04-17 11:00");
insert into Bailler values(16,16,16,"2019-04-17 11:00","2020-04-17 11:00");
insert into Bailler values(17,17,17,"2019-04-17 11:00","2020-04-17 11:00");
insert into Bailler values(18,16,18,"2019-04-17 11:00","2020-04-17 11:00");
insert into Bailler values(19,2,19,"2019-04-17 11:00","2020-04-17 11:00");
insert into Bailler values(20,1,20,"2019-04-17 11:00","2020-04-17 11:00");
insert into Bailler values(21,9,21,"2019-04-17 11:00","2020-04-17 11:00");
insert into Bailler values(22,4,22,"2019-04-17 11:00","2020-04-17 11:00");
insert into Bailler values(23,13,23,"2019-04-17 11:00","2020-04-17 11:00");
insert into Bailler values(24,17,24,"2019-04-17 11:00","2020-04-17 11:00");
insert into Bailler values(25,13,25,"2019-04-17 11:00","2020-04-17 11:00");
insert into Bailler values(26,11,26,"2019-04-17 11:00","2020-04-17 11:00");
insert into Bailler values(27,3,27,"2019-04-17 11:00","2020-04-17 11:00");
insert into Bailler values(28,7,28,"2019-04-17 11:00","2020-04-17 11:00");
insert into Reserver values(1,2,1,"2019-05-18 09:23","2019-05-18 11:23",15,0);
insert into Reserver values(2,2,2,"2019-05-18 09:23","2019-05-18 11:23",15,0);
insert into Reserver values(3,3,3,"2019-07-18 09:23","2019-07-18 11:23",15,0);
insert into Reserver values(4,4,4,"2019-08-18 09:23","2019-09-18 11:23",15,0);
insert into Reserver values(5,5,5,"2019-10-18 09:21","2019-10-18 11:29",15,0);
insert into Reserver values(6,6,6,"2019-11-14 13:00","2019-11-14 15:00",15,0);
insert into Reserver values(7,7,7,"2019-04-19 12:00","2019-04-19 14:00",15,0);
insert into Reserver values(8,8,8,"2019-05-18 09:23","2019-05-18 11:23",15,0);
insert into Reserver values(9,9,9,"2019-07-18 09:23","2019-07-18 11:23",15,0);
insert into Reserver values(10,10,10,"2019-08-18 09:23","2019-09-18 11:23",15,0);
insert into Reserver values(11,11,11,"2019-10-18 09:21","2019-10-18 11:29",15,0);
insert into Reserver values(12,12,12,"2019-11-14 13:00","2019-11-14 15:00",15,0);
insert into Reserver values(13,13,13,"2019-04-19 12:00","2019-04-19 14:00",15,0);
insert into Reserver values(14,14,14,"2019-05-18 09:23","2019-06-18 11:23",15,0);
insert into Reserver values(15,15,15,"2019-07-18 09:23","2019-07-18 11:23",15,0);
insert into Reserver values(16,16,16,"2019-08-18 09:23","2019-09-18 11:23",15,0);
insert into Reserver values(17,17,17,"2019-10-18 09:21","2019-10-18 11:29",15,0);
insert into Reserver values(18,6,18,"2019-11-14 13:00","2019-11-14 15:00",15,0);
insert into Reserver values(19,1,19,"2019-04-18 12:00","2019-04-18 14:00",15,0);
insert into Reserver values(20,2,20,"2019-05-18 09:23","2019-05-18 11:23",15,0);
insert into Reserver values(21,3,21,"2019-07-18 09:23","2019-07-18 11:23",15,0);
insert into Reserver values(22,4,22,"2019-08-18 09:23","2019-08-18 11:23",15,0);
insert into Reserver values(23,5,23,"2019-10-18 09:21","2019-10-18 11:29",15,0);
insert into Reserver values(24,6,24,"2019-11-14 13:00","2019-11-14 15:00",15,0);
insert into Reserver values(25,1,25,"2019-04-19 12:00","2019-04-19 14:00",15,0);
insert into Reserver values(26,2,26,"2019-05-18 09:23","2019-05-18 11:23",15,0);
select * from Vehicule;
DELETE from Vehicule where id_vehicule>11;
select * from Vehicule;
BEGIN TRANSACTION;
/* Create a table called NAMES */
CREATE TABLE SALES( OrderId integer,
OrderDate datetime,
OrderPrice integer,
OrderQty integer,
CustName varchar(30)
);
CREATE TABLE PRODUCTS (
Product_Id varchar(5),
OrderId integer,
Mfg_Date datetime,
Raw_Material varchar(10),
Vendor_Id integer
);
INSERT INTO PRODUCTS (ProductId, OrderId, Mfg_Date, Raw_Material, Vendor_Id )
VALUES
('AZ145',)
/* Create few records in this table */
INSERT INTO SALES(OrderId, OrderDate, OrderPrice, OrderQty, CustName)
VALUES
(1,'12/22/2005',160,2,'Smith'),
(2,'08/10/2005',190,2,'Johnson'),
(3,'07/13/2005',500,5,'Baldwin'),
(4,'07/15/2005',420,2,'Smith'),
(5,'12/22/2005',1000,4,'Wood'),
(6,'10/02/2005',820,4,'Smith'),
(7,'11/03/2005',2000,2,'Baldwin'),
(8,'12/22/2002',1000,4,'Wood'),
(9,'11/03/2005',5000,4,'Smith');
/* Display all the records from the table */
SELECT * FROM SALES;
CREATE TABLE funkcje (
KOD_FUNKCJI char(3) NOT NULL ,
NAZWA_FUNKCJI char(30) NOT NULL
);
CREATE TABLE instytuty (
NR_INST smallint NOT NULL ,
NAZWA_INST char(30)
);
CREATE TABLE kierunki (
NR_KIER smallint NOT NULL ,
NAZWA_KIER char(30) NOT NULL ,
NR_INST smallint NOT NULL
);
CREATE TABLE oceny (
NR_STUD smallint NOT NULL ,
NR_PRZEDM smallint NOT NULL ,
DATA_ZAL datetime NOT NULL ,
TERMIN smallint NOT NULL ,
ZAL_EGZ char(1) NOT NULL ,
OCENA smallint NOT NULL
);
CREATE TABLE pracownicy (
NR_PRAC int NOT NULL ,
PLEC char(1) NOT NULL ,
DATA_UR datetime ,
NAZWISKO char(15) ,
NR_ZESP smallint NOT NULL
);
CREATE TABLE przedmioty (
NR_PRZEDM smallint NOT NULL ,
NAZWA_PRZEDM char(40) NOT NULL ,
KOD_TPRZEDM char(3) NOT NULL ,
NR_PRZEDM_NADRZ smallint ,
NR_ODP_PRAC int NOT NULL ,
NR_KIER smallint NOT NULL
);
CREATE TABLE przydzialy (
NR_PRAC int NOT NULL ,
NR_TEM int NOT NULL ,
KOD_FUNKCJI char(3) NOT NULL
);
CREATE TABLE rozklady (
NR_SALI smallint NOT NULL ,
DZIEN char(3) NOT NULL ,
GODZINA smallint NOT NULL ,
NR_PRZEDM smallint NOT NULL ,
NR_PRAC int NOT NULL
);
CREATE TABLE sale (
NR_SALI smallint NOT NULL ,
ROZM_SALI smallint NOT NULL ,
EKRAN char(1) NOT NULL
);
CREATE TABLE studenci (
NR_STUD smallint NOT NULL ,
NAZWISKO char (20) NOT NULL ,
DATA_UR datetime NOT NULL ,
PLEC char(1) NOT NULL ,
NR_KIER smallint NOT NULL
);
CREATE TABLE tematy (
NR_TEM int NOT NULL ,
TEMAT char(30) NOT NULL ,
DATA_ROZP datetime ,
DATA_ODB datetime ,
NR_PRAC_KT int NOT NULL ,
NR_TEM_NADRZ int
);
CREATE TABLE typy_przedmiotow (
KOD_TPRZEDM char (3) NOT NULL ,
NAZWA_TPRZEDM char (50) NOT NULL
);
CREATE TABLE wyplaty (
NR_PRAC int NOT NULL ,
NR_TEM int NOT NULL ,
DATA_NALICZ datetime NOT NULL ,
DATA_WYPL datetime ,
KWOTA decimal(10, 1) NOT NULL
);
CREATE TABLE zespoly (
NR_ZESP smallint NOT NULL ,
NAZWA_ZESP char(30) NOT NULL ,
NR_PRAC_KZ int NOT NULL ,
NR_INST smallint NOT NULL
);
insert into funkcje values('ADM','Administrator ');
insert into funkcje values('ANL','Analityk');
insert into funkcje values('KRW','Kierownik ');
insert into funkcje values('PRG','Programista ');
insert into funkcje values('PRJ','Projektant');
insert into funkcje values('TST','Tester');
insert into instytuty values(1,'INFORMATYKI ');
insert into instytuty values(2,'ELEKTRONIKI ');
insert into instytuty values(3,'AUTOMATYKI');
insert into instytuty values(4,'BUDOWNICTWA ');
insert into instytuty values(5,'GORNICTWA ');
insert into instytuty values(6,'HUTNICTWA ');
insert into kierunki values(1,'OPROGRAMOWANIE',1);
insert into kierunki values(2,'BUDOWA MC ',1);
insert into kierunki values(3,'INFORMATYKA ',1);
insert into kierunki values(4,'ELEKTRONIKA MED ',2);
insert into kierunki values(5,'TECHNOLOGIA EL',2);
insert into kierunki values(6,'ROBOTYKA',3);
insert into kierunki values(7,'STEROWANIE PROC ',3);
insert into kierunki values(8,'EUC ',3);
insert into kierunki values(9,'GORNICTWA ODKRYWKOWEGO',4);
insert into kierunki values(10,'RESTRUKTURYZACJI GORNICTWA',4);
insert into oceny values(1,1,'1999-01-01',1,'E',5);
insert into oceny values(2,1,'1999-01-04',1,'E',4);
insert into oceny values(15,1,'2000-03-04',1,'E',4);
insert into oceny values(23,1,'2000-06-06',1,'E',5);
insert into oceny values(38,1,'2000-04-09',1,'E',3);
insert into oceny values(38,1,'2000-05-05',1,'Z',4);
insert into oceny values(53,1,'2000-07-08',1,'E',3);
insert into oceny values(54,1,'2000-07-09',1,'E',3);
insert into oceny values(63,1,'2000-01-07',1,'Z',4);
insert into oceny values(63,1,'2000-02-07',1,'E',2);
insert into oceny values(1,20,'2000-04-04',1,'E',5);
insert into oceny values(2,20,'2000-05-04',1,'E',4);
insert into oceny values(3,20,'2000-06-06',1,'E',4);
insert into oceny values(4,20,'2000-07-07',1,'E',5);
insert into oceny values(5,30,'2000-08-08',1,'E',5);
insert into oceny values(6,30,'2000-04-09',1,'E',3);
insert into oceny values(7,30,'2000-03-04',1,'E',4);
insert into oceny values(8,30,'2000-05-05',1,'Z',4);
insert into oceny values(8,30,'2000-06-06',1,'E',3);
insert into oceny values(8,40,'2000-07-08',1,'E',3);
insert into oceny values(9,40,'2000-07-09',1,'E',3);
insert into oceny values(10,40,'2000-01-04',1,'Z',4);
insert into oceny values(10,40,'2000-02-22',1,'E',4);
insert into oceny values(10,40,'2000-03-17',2,'E',4);
insert into oceny values(12,40,'2000-01-25',1,'E',2);
insert into oceny values(21,40,'2000-08-09',1,'E',2);
insert into oceny values(23,40,'2000-05-01',1,'E',5);
insert into oceny values(27,40,'2000-07-08',1,'E',4);
insert into oceny values(45,40,'2000-02-03',1,'E',5);
insert into oceny values(9,50,'2000-02-07',1,'E',5);
insert into oceny values(12,50,'2000-02-05',1,'E',3);
insert into oceny values(13,50,'2000-02-16',1,'E',5);
insert into oceny values(19,50,'2000-04-18',1,'E',4);
insert into oceny values(20,50,'2000-04-16',1,'E',5);
insert into oceny values(24,50,'2000-08-28',1,'E',4);
insert into oceny values(41,50,'2000-05-03',1,'E',5);
insert into oceny values(44,50,'2000-08-03',1,'E',3);
insert into oceny values(48,50,'2000-07-25',1,'E',3);
insert into oceny values(10,60,'2000-01-07',1,'E',5);
insert into oceny values(11,60,'2000-03-14',1,'E',3);
insert into oceny values(12,60,'2000-02-19',1,'E',5);
insert into oceny values(13,60,'2000-03-17',1,'E',5);
insert into oceny values(18,60,'2000-08-28',1,'E',2);
insert into oceny values(18,60,'2000-09-24',2,'E',3);
insert into oceny values(21,60,'2000-09-11',1,'E',3);
insert into oceny values(33,60,'2000-04-21',1,'E',5);
insert into oceny values(41,60,'2000-05-05',1,'E',4);
insert into oceny values(44,60,'2000-03-04',1,'E',5);
insert into oceny values(47,60,'2000-07-24',1,'E',4);
insert into oceny values(10,70,'2000-01-12',1,'E',2);
insert into oceny values(11,70,'2000-01-29',1,'E',2);
insert into oceny values(11,70,'2000-07-09',2,'E',2);
insert into oceny values(12,70,'2000-04-25',1,'E',4);
insert into oceny values(15,70,'2000-01-11',1,'E',2);
insert into oceny values(22,70,'2000-05-15',1,'E',2);
insert into oceny values(32,70,'2000-03-05',1,'E',3);
insert into oceny values(43,70,'2000-03-24',1,'E',4);
insert into oceny values(49,70,'2000-08-16',1,'E',3);
insert into oceny values(50,70,'2000-09-16',1,'E',4);
insert into oceny values(10,80,'2000-05-05',1,'E',3);
insert into oceny values(17,80,'2000-02-22',1,'E',4);
insert into oceny values(21,80,'2000-04-15',1,'E',5);
insert into oceny values(23,80,'2000-05-05',1,'E',4);
insert into oceny values(26,80,'2000-06-08',1,'E',4);
insert into oceny values(33,80,'2000-03-23',1,'E',4);
insert into oceny values(10,90,'2000-01-18',1,'Z',3);
insert into oceny values(10,90,'2000-04-28',1,'E',5);
insert into oceny values(10,90,'2000-09-23',2,'E',3);
insert into oceny values(14,90,'2000-01-15',1,'E',3);
insert into oceny values(16,90,'2000-02-11',1,'E',4);
insert into oceny values(19,90,'2000-04-17',1,'E',5);
insert into oceny values(20,90,'2000-03-17',1,'E',2);
insert into oceny values(10,100,'2000-08-08',1,'E',4);
insert into oceny values(25,100,'2000-04-08',1,'E',3);
insert into oceny values(28,100,'2000-08-18',1,'E',4);
insert into oceny values(10,110,'2000-01-05',1,'Z',4);
insert into oceny values(10,110,'2000-01-07',1,'E',3);
insert into oceny values(10,110,'2000-04-23',2,'E',5);
insert into oceny values(14,110,'2000-04-04',1,'E',4);
insert into oceny values(34,110,'2000-03-01',1,'E',5);
insert into oceny values(43,110,'2000-07-05',1,'E',3);
insert into oceny values(48,110,'2000-05-04',1,'E',4);
insert into oceny values(50,110,'2000-05-01',1,'Z',4);
insert into oceny values(50,110,'2000-09-14',1,'E',3);
insert into oceny values(50,110,'2000-09-16',2,'E',3);
insert into pracownicy values(1,'M','1948-09-12','GRZYBEK',4);
insert into pracownicy values(2,'K','1957-02-17','GRZYBIARZ',5);
insert into pracownicy values(3,'M','1965-11-06','WIREK',6);
insert into pracownicy values(4,'K','1959-10-07','JANECZEK ',5);
insert into pracownicy values(5,'M','1961-03-15','TADECZEK ',4);
insert into pracownicy values(6,'M','1949-09-17','PODWISLAK',3);
insert into pracownicy values(7,'K','1947-08-19','WOJTECKA ',2);
insert into pracownicy values(8,'K','1948-07-28','WUJEK',1);
insert into pracownicy values(9,'K','1964-05-07','TRUMAN ',5);
insert into pracownicy values(10,'K','1962-05-04','ZUBEK',2);
insert into pracownicy values(11,'M','1956-04-10','SKRZYPEK ',5);
insert into pracownicy values(12,'K','1968-11-03','POPKO',6);
insert into pracownicy values(13,'M','1945-08-21','NOWY ',1);
insert into pracownicy values(14,'K','1949-08-17','RAZOWA ',3);
insert into pracownicy values(15,'K','1960-09-16','FULAK',4);
insert into pracownicy values(16,'M','1953-09-13','KOTULA ',2);
insert into pracownicy values(17,'M','1951-01-25','KULANEK',3);
insert into pracownicy values(18,'M','1961-07-25','KULAWIK',6);
insert into pracownicy values(19,'M','1958-05-08','SKOCZEK',5);
insert into pracownicy values(20,'K','1952-11-04','WIEZYCKA ',1);
insert into pracownicy values(21,'M','1953-02-23','BUJAK',3);
insert into pracownicy values(22,'K','1947-05-09','BIERNAT',5);
insert into pracownicy values(23,'M','1946-10-10','LUBELSKI ',1);
insert into pracownicy values(24,'M','1963-11-03','IWAN ',2);
insert into pracownicy values(25,'K','1959-03-17','JASKOLA',5);
insert into pracownicy values(26,'K','1950-10-06','REBUS',3);
insert into pracownicy values(27,'K','1947-04-09','REDOKS ',1);
insert into pracownicy values(28,'K','1952-03-14','SKOREK ',4);
insert into pracownicy values(29,'M','1955-10-11','KIPER',5);
insert into pracownicy values(30,'K','1957-10-09','REPIK',2);
insert into pracownicy values(31,'M','1964-11-11','NIEZALEZNY ',1);
insert into pracownicy values(32,'M','1945-12-10','WILK ',3);
insert into pracownicy values(33,'M','1934-10-05','MISIURA',4);
insert into pracownicy values(34,'M','1954-09-04','KRAUS',5);
insert into pracownicy values(35,'M','1934-12-25','WILCZEK',6);
insert into pracownicy values(36,'M','1945-12-10','WILCZYNSKI ',7);
insert into pracownicy values(37,'M','1934-10-05','GABKA',7);
insert into pracownicy values(38,'M','1954-09-04','KRAK ',8);
insert into pracownicy values(39,'M','1934-12-25','SZCZERBIEC ',8);
insert into pracownicy values(40,'M','1984-10-05','PSTROWSKI',7);
insert into pracownicy values(41,'M','1934-12-25','SZCZERBIEC ',8);
insert into przedmioty values(1,'BAZY DANYCH ','WYK',null,1,1);
insert into przedmioty values(2,'BAZY DANYCH ','LAB',1,21,1);
insert into przedmioty values(20,'ARCHITEKTURA KOMPUTEROW ','WYK',40,4,1);
insert into przedmioty values(30,'MODELOWANIE CYFROWE ','WYK',null,4,1);
insert into przedmioty values(40,'PODSTAWY INFORMATYKI','WYK',null,1,1);
insert into przedmioty values(50,'METODY NUMERYCZNE ','WYK',null,4,1);
insert into przedmioty values(60,'ORACLE','WYK',1,22,1);
insert into przedmioty values(70,'TABAKS','WYK',1,24,2);
insert into przedmioty values(80,'AIPSK ','WYK',null,11,2);
insert into przedmioty values(90,'ROZPROSZONE BAZY DANYCH ','WYK',null,2,2);
insert into przedmioty values(100,'ANALIZA MATEMATYCZNA','WYK',null,3,2);
insert into przedmioty values(110,'ALGEBRA ','WYK',null,1,2);
insert into przydzialy values(1,1,'PRG');
insert into przydzialy values(1,4,'ANL');
insert into przydzialy values(1,5,'ADM');
insert into przydzialy values(1,10,'ANL');
insert into przydzialy values(1,11,'ADM');
insert into przydzialy values(1,11,'PRG');
insert into przydzialy values(1,11,'PRJ');
insert into przydzialy values(1,13,'PRG');
insert into przydzialy values(1,16,'ANL');
insert into przydzialy values(1,21,'TST');
insert into przydzialy values(1,25,'PRJ');
insert into przydzialy values(1,33,'KRW');
insert into przydzialy values(1,101,'ANL');
insert into przydzialy values(1,101,'PRJ');
insert into przydzialy values(1,103,'PRG');
insert into przydzialy values(1,104,'ANL');
insert into przydzialy values(1,104,'PRG');
insert into przydzialy values(1,106,'KRW');
insert into przydzialy values(1,107,'ANL');
insert into przydzialy values(1,107,'PRJ');
insert into przydzialy values(2,2,'PRJ');
insert into przydzialy values(2,3,'PRJ');
insert into przydzialy values(2,13,'PRG');
insert into przydzialy values(2,15,'ANL');
insert into przydzialy values(2,16,'PRG');
insert into przydzialy values(2,18,'PRJ');
insert into przydzialy values(2,101,'PRG');
insert into przydzialy values(2,104,'PRG');
insert into przydzialy values(2,104,'PRJ');
insert into przydzialy values(2,105,'PRG');
insert into przydzialy values(3,31,'ADM');
insert into przydzialy values(3,31,'ANL');
insert into przydzialy values(3,31,'PRG');
insert into przydzialy values(3,31,'PRJ');
insert into przydzialy values(4,3,'TST');
insert into przydzialy values(4,5,'PRJ');
insert into przydzialy values(4,6,'PRG');
insert into przydzialy values(4,11,'ADM');
insert into przydzialy values(4,25,'ANL');
insert into przydzialy values(4,106,'ADM');
insert into przydzialy values(4,106,'PRJ');
insert into przydzialy values(5,2,'PRJ');
insert into przydzialy values(5,3,'PRJ');
insert into przydzialy values(5,13,'PRG');
insert into przydzialy values(5,18,'ADM');
insert into przydzialy values(5,18,'PRG');
insert into przydzialy values(5,103,'PRJ');
insert into przydzialy values(5,106,'PRG');
insert into przydzialy values(6,5,'PRG');
insert into przydzialy values(6,8,'ANL');
insert into przydzialy values(6,8,'TST');
insert into przydzialy values(6,10,'PRJ');
insert into przydzialy values(6,17,'PRJ');
insert into przydzialy values(6,21,'PRG');
insert into przydzialy values(6,24,'TST');
insert into przydzialy values(7,39,'PRG');
insert into przydzialy values(7,39,'TST');
insert into przydzialy values(8,6,'PRG');
insert into przydzialy values(8,8,'ADM');
insert into przydzialy values(8,13,'PRG');
insert into przydzialy values(8,14,'ANL');
insert into przydzialy values(8,16,'PRJ');
insert into przydzialy values(8,22,'PRJ');
insert into przydzialy values(8,28,'PRG');
insert into przydzialy values(9,13,'PRJ');
insert into przydzialy values(9,14,'ANL');
insert into przydzialy values(9,18,'PRG');
insert into przydzialy values(9,18,'TST');
insert into przydzialy values(9,28,'PRG');
insert into przydzialy values(9,104,'ADM');
insert into przydzialy values(9,104,'PRG');
insert into przydzialy values(9,107,'PRG');
insert into przydzialy values(10,24,'KRW');
insert into przydzialy values(10,101,'KRW');
insert into przydzialy values(11,2,'PRJ');
insert into przydzialy values(11,15,'KRW');
insert into przydzialy values(11,18,'ANL');
insert into przydzialy values(11,21,'KRW');
insert into przydzialy values(11,102,'ANL');
insert into przydzialy values(11,104,'PRG');
insert into przydzialy values(11,105,'PRG');
insert into przydzialy values(11,106,'ADM');
insert into przydzialy values(12,3,'ADM');
insert into przydzialy values(12,3,'PRG');
insert into przydzialy values(12,4,'PRG');
insert into przydzialy values(12,20,'ANL');
insert into przydzialy values(12,20,'PRG');
insert into przydzialy values(12,31,'PRG');
insert into przydzialy values(12,40,'PRG');
insert into przydzialy values(12,105,'PRJ');
insert into przydzialy values(13,2,'PRJ');
insert into przydzialy values(13,10,'PRJ');
insert into przydzialy values(13,17,'PRG');
insert into przydzialy values(13,21,'PRG');
insert into przydzialy values(13,22,'ANL');
insert into przydzialy values(13,28,'ANL');
insert into przydzialy values(13,104,'TST');
insert into przydzialy values(14,40,'ANL');
insert into przydzialy values(15,6,'PRG');
insert into przydzialy values(15,6,'PRJ');
insert into przydzialy values(15,15,'ANL');
insert into przydzialy values(15,20,'ANL');
insert into przydzialy values(15,22,'ANL');
insert into przydzialy values(15,31,'ANL');
insert into przydzialy values(15,104,'ANL');
insert into przydzialy values(15,106,'PRG');
insert into przydzialy values(16,5,'PRG');
insert into przydzialy values(16,10,'PRJ');
insert into przydzialy values(16,13,'PRG');
insert into przydzialy values(16,15,'TST');
insert into przydzialy values(16,17,'PRJ');
insert into przydzialy values(16,22,'TST');
insert into przydzialy values(16,28,'PRJ');
insert into przydzialy values(18,6,'ADM');
insert into przydzialy values(18,8,'PRG');
insert into przydzialy values(18,14,'ADM');
insert into przydzialy values(18,17,'PRG');
insert into przydzialy values(18,21,'ANL');
insert into przydzialy values(18,32,'KRW');
insert into przydzialy values(18,103,'ADM');
insert into przydzialy values(18,105,'PRG');
insert into przydzialy values(19,8,'PRG');
insert into przydzialy values(19,18,'PRG');
insert into przydzialy values(19,106,'TST');
insert into przydzialy values(19,107,'ANL');
insert into przydzialy values(20,3,'ANL');
insert into przydzialy values(20,4,'TST');
insert into przydzialy values(20,5,'PRG');
insert into przydzialy values(20,6,'PRG');
insert into przydzialy values(20,20,'PRJ');
insert into przydzialy values(20,30,'PRJ');
insert into przydzialy values(20,39,'ANL');
insert into przydzialy values(20,42,'PRG');
insert into przydzialy values(20,104,'ANL');
insert into przydzialy values(20,106,'PRJ');
insert into przydzialy values(21,8,'KRW');
insert into przydzialy values(21,30,'KRW');
insert into przydzialy values(21,42,'ANL');
insert into przydzialy values(21,44,'KRW');
insert into przydzialy values(22,3,'ANL');
insert into przydzialy values(22,4,'PRG');
insert into przydzialy values(22,11,'PRG');
insert into przydzialy values(22,22,'PRG');
insert into przydzialy values(22,25,'PRG');
insert into przydzialy values(22,103,'PRG');
insert into przydzialy values(23,14,'ANL');
insert into przydzialy values(23,20,'PRG');
insert into przydzialy values(23,28,'ANL');
insert into przydzialy values(23,28,'PRJ');
insert into przydzialy values(23,42,'KRW');
insert into przydzialy values(23,43,'KRW');
insert into przydzialy values(23,101,'PRG');
insert into przydzialy values(23,104,'ANL');
insert into przydzialy values(23,104,'PRJ');
insert into przydzialy values(23,106,'PRG');
insert into przydzialy values(25,1,'PRG');
insert into przydzialy values(25,1,'PRJ');
insert into przydzialy values(25,11,'PRG');
insert into przydzialy values(25,16,'KRW');
insert into przydzialy values(25,20,'ANL');
insert into przydzialy values(25,22,'PRG');
insert into przydzialy values(25,24,'ADM');
insert into przydzialy values(25,39,'PRJ');
insert into przydzialy values(25,102,'KRW');
insert into przydzialy values(26,2,'PRJ');
insert into przydzialy values(26,6,'PRG');
insert into przydzialy values(26,10,'KRW');
insert into przydzialy values(26,17,'PRG');
insert into przydzialy values(26,17,'TST');
insert into przydzialy values(26,24,'ADM');
insert into przydzialy values(26,103,'PRG');
insert into przydzialy values(26,106,'PRG');
insert into przydzialy values(27,4,'ANL');
insert into przydzialy values(27,15,'PRG');
insert into przydzialy values(27,15,'TST');
insert into przydzialy values(27,18,'ADM');
insert into przydzialy values(27,22,'PRG');
insert into przydzialy values(27,24,'ANL');
insert into przydzialy values(27,25,'KRW');
insert into przydzialy values(29,6,'KRW');
insert into przydzialy values(29,8,'PRG');
insert into przydzialy values(29,11,'KRW');
insert into przydzialy values(29,16,'PRG');
insert into przydzialy values(29,24,'PRJ');
insert into przydzialy values(29,25,'PRJ');
insert into przydzialy values(29,28,'KRW');
insert into przydzialy values(29,102,'PRG');
insert into przydzialy values(29,104,'PRG');
insert into przydzialy values(30,4,'TST');
insert into przydzialy values(30,10,'PRG');
insert into przydzialy values(30,22,'PRG');
insert into przydzialy values(30,24,'ADM');
insert into przydzialy values(30,24,'PRG');
insert into przydzialy values(30,40,'ANL');
insert into przydzialy values(30,40,'PRG');
insert into przydzialy values(30,104,'TST');
insert into przydzialy values(30,105,'PRG');
insert into przydzialy values(30,105,'TST');
insert into przydzialy values(31,2,'PRG');
insert into przydzialy values(31,13,'KRW');
insert into przydzialy values(31,17,'PRJ');
insert into przydzialy values(31,22,'KRW');
insert into przydzialy values(31,31,'KRW');
insert into przydzialy values(31,100,'KRW');
insert into przydzialy values(32,1,'KRW');
insert into przydzialy values(32,2,'KRW');
insert into przydzialy values(32,5,'KRW');
insert into przydzialy values(32,10,'PRG');
insert into przydzialy values(32,18,'KRW');
insert into przydzialy values(32,22,'PRG');
insert into przydzialy values(32,39,'KRW');
insert into przydzialy values(32,45,'KRW');
insert into przydzialy values(32,107,'KRW');
insert into przydzialy values(33,1,'PRJ');
insert into przydzialy values(33,8,'PRG');
insert into przydzialy values(33,18,'PRG');
insert into przydzialy values(33,36,'KRW');
insert into przydzialy values(33,103,'KRW');
insert into przydzialy values(33,105,'KRW');
insert into przydzialy values(34,3,'KRW');
insert into przydzialy values(34,14,'KRW');
insert into przydzialy values(34,20,'KRW');
insert into przydzialy values(34,22,'PRJ');
insert into przydzialy values(34,30,'TST');
insert into przydzialy values(35,4,'KRW');
insert into przydzialy values(35,10,'PRG');
insert into przydzialy values(35,17,'KRW');
insert into przydzialy values(35,104,'KRW');
insert into przydzialy values(36,5,'PRG');
insert into przydzialy values(36,10,'ANL');
insert into przydzialy values(36,34,'KRW');
insert into przydzialy values(36,37,'KRW');
insert into przydzialy values(36,40,'PRG');
insert into przydzialy values(36,40,'PRJ');
insert into przydzialy values(37,10,'ANL');
insert into przydzialy values(37,35,'KRW');
insert into przydzialy values(37,38,'KRW');
insert into przydzialy values(37,40,'ANL');
insert into przydzialy values(37,101,'PRG');
insert into przydzialy values(38,40,'KRW');
insert into przydzialy values(38,41,'KRW');
insert into przydzialy values(40,31,'PRG');
insert into przydzialy values(41,31,'ADM');
insert into rozklady values(1,'CZW',10,60,22);
insert into rozklady values(1,'PON',12,1,1);
insert into rozklady values(1,'WTO',12,60,34);
insert into rozklady values(1,'WTO',16,110,1);
insert into rozklady values(2,'PON',16,110,7);
insert into rozklady values(2,'SRO',12,20,32);
insert into rozklady values(2,'WTO',12,50,4);
insert into rozklady values(2,'WTO',18,30,23);
insert into rozklady values(3,'CZW',8,50,6);
insert into rozklady values(3,'PON',8,20,12);
insert into rozklady values(3,'PTK',16,1,20);
insert into rozklady values(4,'PON',10,110,8);
insert into rozklady values(4,'PON',14,70,28);
insert into rozklady values(4,'SRO',8,20,4);
insert into rozklady values(4,'WTO',10,90,2);
insert into rozklady values(5,'SRO',10,30,12);
insert into rozklady values(6,'SRO',12,70,24);
insert into rozklady values(7,'PTK',10,80,11);
insert into rozklady values(7,'WTO',12,1,21);
insert into rozklady values(8,'CZW',10,20,22);
insert into rozklady values(8,'PTK',8,90,10);
insert into rozklady values(9,'PTK',14,80,11);
insert into rozklady values(9,'WTO',8,1,33);
insert into rozklady values(10,'WTO',12,30,4);
insert into rozklady values(11,'CZW',10,100,3);
insert into rozklady values(11,'PON',10,1,34);
insert into rozklady values(12,'WTO',15,2,21);
insert into rozklady values(15,'CZW',14,1,4);
insert into rozklady values(16,'CZW',8,40,2);
insert into rozklady values(16,'PON',10,40,1);
insert into rozklady values(16,'SRO',10,100,6);
insert into sale values(1,20,'T');
insert into sale values(2,12,'T');
insert into sale values(3,23,'T');
insert into sale values(4,32,'T');
insert into sale values(5,33,'N');
insert into sale values(6,12,'N');
insert into sale values(7,30,'N');
insert into sale values(8,11,'N');
insert into sale values(9,23,'N');
insert into sale values(10,17,'N');
insert into sale values(11,19,'N');
insert into sale values(12,18,'N');
insert into sale values(13,21,'N');
insert into sale values(14,20,'N');
insert into sale values(15,24,'N');
insert into sale values(16,30,'N');
insert into studenci values(1,'MARCZAK ','1968-09-13','M',2);
insert into studenci values(2,'WALCZAK ','1969-06-02','K',3);
insert into studenci values(3,'JANCZURA','1964-10-07','M',1);
insert into studenci values(4,'KRAUS ','1968-05-03','M',1);
insert into studenci values(5,'MISIURA ','1966-03-19','K',2);
insert into studenci values(6,'WILK','1960-09-11','K',4);
insert into studenci values(7,'KOT ','1962-08-19','K',1);
insert into studenci values(8,'KOWALSKI','1963-07-28','M',1);
insert into studenci values(9,'DORECKA ','1960-06-01','K',1);
insert into studenci values(10,'DYBA','1969-09-12','M',1);
insert into studenci values(11,'SIWEK ','1967-02-24','M',2);
insert into studenci values(12,'CZARNY','1960-02-25','K',1);
insert into studenci values(13,'BABIK ','1969-11-02','K',1);
insert into studenci values(14,'RYNEK ','1965-04-14','M',2);
insert into studenci values(15,'TUREK ','1968-04-13','K',3);
insert into studenci values(16,'MAJAKOWSKI','1964-10-10','M',2);
insert into studenci values(17,'MAJ ','1960-08-21','K',2);
insert into studenci values(18,'DUZY','1963-03-18','M',1);
insert into studenci values(19,'WASZUT','1969-07-22','M',3);
insert into studenci values(20,'WIREK ','1965-11-06','M',4);
insert into studenci values(21,'WALAS ','1967-07-24','M',3);
insert into studenci values(22,'UNIK','1964-07-28','K',3);
insert into studenci values(23,'UCHLIK','1963-10-08','M',3);
insert into studenci values(24,'OPAS','1962-05-09','K',2);
insert into studenci values(25,'SZAMOT','1964-01-27','K',3);
insert into studenci values(26,'SOSNA ','1960-10-11','M',3);
insert into studenci values(27,'POZNANSKI ','1964-03-17','M',2);
insert into studenci values(28,'TURECKA ','1963-01-28','K',3);
insert into studenci values(29,'PIOTRUS ','1961-10-10','M',2);
insert into studenci values(30,'POPKO ','1968-11-03','K',2);
insert into studenci values(31,'LIPECKI ','1967-05-04','M',1);
insert into studenci values(32,'LISIECKA','1961-02-20','K',2);
insert into studenci values(33,'LIS ','1961-04-10','K',1);
insert into studenci values(34,'SIWACZEK','1962-04-09','K',2);
insert into studenci values(35,'MODELAK ','1963-05-08','K',2);
insert into studenci values(36,'JANIK ','1966-03-15','M',1);
insert into studenci values(37,'KORUS ','1964-09-17','K',1);
insert into studenci values(38,'STAWARZ ','1962-10-09','M',3);
insert into studenci values(39,'DYDUCH','1962-02-19','M',1);
insert into studenci values(40,'TRUMAN','1964-05-07','K',3);
insert into studenci values(41,'MANIERAK','1965-09-16','K',2);
insert into studenci values(42,'WILCZYNSKI','1961-08-20','M',3);
insert into studenci values(43,'WARECKA ','1967-02-24','K',3);
insert into studenci values(44,'UGASZ ','1960-02-21','M',3);
insert into studenci values(45,'URGACZ','1961-03-20','K',3);
insert into studenci values(46,'MICHAL','1966-01-25','M',2);
insert into studenci values(47,'MOMATIUK','1967-11-04','M',2);
insert into studenci values(48,'CZARNYNOGA','1965-01-26','M',1);
insert into studenci values(49,'FILUT ','1966-09-15','M',1);
insert into studenci values(50,'OKRASA','1968-02-23','K',2);
insert into studenci values(51,'WAREK ','1953-10-08','M',5);
insert into studenci values(52,'PANKRACY','1950-02-21','M',5);
insert into studenci values(53,'MUZYKANT','1954-07-28','K',6);
insert into studenci values(54,'PLASTUS ','1951-03-20','K',6);
insert into studenci values(55,'REKSIK','1957-07-24','M',7);
insert into studenci values(56,'FASOLA','1959-06-02','K',7);
insert into studenci values(57,'KUCHCIK ','1957-02-24','K',8);
insert into studenci values(58,'KICHUS','1959-07-22','M',8);
insert into studenci values(59,'BAZGROLA','1973-08-20','M',9);
insert into studenci values(60,'SZALEJ','1972-09-11','K',9);
insert into studenci values(61,'MIKUS ','1971-07-24','M',10);
insert into studenci values(62,'MILIONER','1970-06-02','K',10);
insert into studenci values(63,'GLABIK','1954-07-28','K',6);
insert into studenci values(64,'ZIMNY ','1951-03-20','K',6);
insert into studenci values(65,'MILIONER','1971-07-24','K',10);
insert into studenci values(66,'MILIONER','1970-06-02','K',10);
insert into tematy values(1,'ADA SRODOWISKO','1990-01-01','1991-12-31',32,null);
insert into tematy values(2,'ADA EDYTOR','1990-03-01','1990-12-31',33,1);
insert into tematy values(3,'ADA KOMPILATOR','1990-09-01','1991-10-31',34,1);
insert into tematy values(4,'PASCAL KOMPILATOR ','1983-03-21','1984-03-20',35,null);
insert into tematy values(5,'BAZA DANYCH - SPOLEM','1987-04-20','1987-09-17',32,null);
insert into tematy values(6,'BAZA DANYCH - PZU ','1986-03-10','1986-07-28',29,null);
insert into tematy values(8,'PROCEDURY GRAFICZNE ','1992-04-24','1992-08-22',21,null);
insert into tematy values(10,'PROJEKTOWANIE UKLADOW ','1983-12-11','1984-03-20',26,null);
insert into tematy values(11,'ANALIZATOR WIDMA','1987-06-19','1987-11-16',29,null);
insert into tematy values(13,'LISP - KOMPILATOR ','1985-01-08','1985-03-19',31,null);
insert into tematy values(14,'PROLOG - KOMPILATOR ','1992-06-23','1992-08-22',34,null);
insert into tematy values(15,'STEROWANIE GAZOCIAGIEM','1983-08-22','1983-11-10',11,null);
insert into tematy values(16,'STEROWANIE TASMOCIAGIEM ','1984-02-09','1984-03-20',25,null);
insert into tematy values(17,'STEROWNIK TURBINY W ELEKTROWNI','1987-08-18','1987-10-17',35,null);
insert into tematy values(18,'KARTA EMULATORA PC','1986-07-08','1986-09-26',32,null);
insert into tematy values(20,'GENERATOR SYGNALOW W.CZ.','1992-08-22','1992-11-20',34,null);
insert into tematy values(21,'ANALIZATOR STANOW ','1983-10-21','1983-11-10',11,null);
insert into tematy values(22,'KANAL WIZJI - OPROGRAMOWANIE','1984-04-09','1984-05-19',31,null);
insert into tematy values(24,'CZUJNIK CISNIENIA ','1986-09-06','1986-10-26',10,null);
insert into tematy values(25,'ANALIZATOR STEZENIA JONOW ','1985-05-08','1985-06-17',27,null);
insert into tematy values(28,'WZMACNIACZ W.CZ.','1984-06-08','1984-12-15',29,null);
insert into tematy values(30,'MONITOR ZNAKOWY ','1986-11-05','1986-11-25',21,null);
insert into tematy values(31,'DYSK','1985-04-01','1985-08-09',31,null);
insert into tematy values(32,'STEROWANIE REAKTOREM','1994-07-01','1994-03-03',18,null);
insert into tematy values(33,'GUPTA ','1999-06-25','1999-02-15',1,null);
insert into tematy values(34,'PRZETWARZANIE WEKTOROWE ','1986-12-15','1986-07-28',36,null);
insert into tematy values(35,'PRZETWARZANIE ROWNOLEGLE','1985-08-16','1985-03-19',37,null);
insert into tematy values(36,'PRZETWARZANIE ROZPROSZONE ','1993-01-29','1992-08-22',33,null);
insert into tematy values(37,'ASSEMBLERY','1984-03-29','1983-10-11',36,null);
insert into tematy values(38,'SYMULATOR ','1984-09-16','1984-03-20',37,null);
insert into tematy values(39,'BEZPIECZENSTWO','1988-03-25','1988-06-13',32,null);
insert into tematy values(40,'ODTWARZANIE ','1987-02-13','1987-06-23',38,null);
insert into tematy values(41,'ARCHIWIZACJA','1985-07-10','1984-12-12',38,null);
insert into tematy values(42,'SLABOPLATNY ','2000-06-18','2000-07-08',23,null);
insert into tematy values(43,'ZEROWY','2000-06-28','1999-11-11',23,null);
insert into tematy values(44,'KANALY WIZJI','1984-06-07','1983-10-11',21,null);
insert into tematy values(45,'KARTA MONITORA','1985-11-24','1985-03-19',32,null);
insert into tematy values(100,'ZESTAW KOMPUTEROWY','1985-01-01','1988-10-31',31,null);
insert into tematy values(101,'KARTA PROCESORA ','1988-10-15','1989-01-13',10,100);
insert into tematy values(102,'MONITOR GRAFICZNY ','1987-06-17','1987-07-07',25,100);
insert into tematy values(103,'GENERATOR DZWIEKU ','1987-06-27','1987-08-06',33,100);
insert into tematy values(104,'STEROWNIK DYSKU ','1990-01-04','1990-03-05',35,100);
insert into tematy values(105,'KARTA MONITORA GRAFICZNEGO','1991-02-28','1991-04-19',33,100);
insert into tematy values(106,'MODUL I/O ','1988-02-18','1988-03-29',1,100);
insert into tematy values(107,'ZASILACZ','1990-02-03','1990-03-05',32,100);
insert into typy_przedmiotow values('CWC','Ćwiczenia ');
insert into typy_przedmiotow values('LAB','Laboratorium');
insert into typy_przedmiotow values('PRJ','Projekt ');
insert into typy_przedmiotow values('SMN','Seminarium');
insert into typy_przedmiotow values('WYK','Wykład');
insert into wyplaty values(1,1,'1990-01-16','1990-01-17',420.0);
insert into wyplaty values(1,4,'1983-04-05','1983-04-06',320.0);
insert into wyplaty values(1,5,'1987-05-05','1987-05-06',490.0);
insert into wyplaty values(1,10,'1983-12-26','1983-12-27',330.0);
insert into wyplaty values(1,11,'1987-07-04','1987-07-05',340.0);
insert into wyplaty values(1,11,'1987-09-04','1987-09-05',790.0);
insert into wyplaty values(1,11,'1987-11-04','1987-11-05',230.0);
insert into wyplaty values(1,13,'1985-01-23','1985-01-24',1310.0);
insert into wyplaty values(1,16,'1984-02-24','1984-02-25',830.0);
insert into wyplaty values(1,21,'1983-11-05','1983-11-06',610.0);
insert into wyplaty values(1,25,'1985-05-23','1985-05-24',460.0);
insert into wyplaty values(1,101,'1988-10-30','1988-10-31',610.0);
insert into wyplaty values(1,101,'1988-12-30','1988-12-31',760.0);
insert into wyplaty values(1,103,'1987-07-12','1987-07-13',780.0);
insert into wyplaty values(1,104,'1990-01-19','1990-01-20',560.0);
insert into wyplaty values(1,104,'1990-03-19','1990-03-20',310.0);
insert into wyplaty values(1,107,'1990-02-18','1990-02-19',270.0);
insert into wyplaty values(1,107,'1990-04-18','1990-04-19',260.0);
insert into wyplaty values(2,2,'1990-03-16','1990-03-17',600.0);
insert into wyplaty values(2,3,'1990-09-16','1990-09-17',420.0);
insert into wyplaty values(2,13,'1985-01-23','1985-01-24',200.0);
insert into wyplaty values(2,15,'1983-09-06','1983-09-07',310.0);
insert into wyplaty values(2,16,'1984-02-24','1984-02-25',510.0);
insert into wyplaty values(2,18,'1986-07-23','1986-07-24',270.0);
insert into wyplaty values(2,101,'1988-10-30','1988-10-31',260.0);
insert into wyplaty values(2,104,'1990-01-19','1990-01-20',560.0);
insert into wyplaty values(2,104,'1990-03-19','1990-03-20',390.0);
insert into wyplaty values(2,105,'1991-03-15','1991-03-16',460.0);
insert into wyplaty values(3,31,'1985-04-16','1985-04-17',760.0);
insert into wyplaty values(3,31,'1985-04-17','1985-04-18',610.0);
insert into wyplaty values(3,31,'1985-06-16','1985-06-17',230.0);
insert into wyplaty values(3,31,'1985-07-16','1985-07-17',990.0);
insert into wyplaty values(4,3,'1990-09-16','1990-09-17',440.0);
insert into wyplaty values(4,5,'1987-05-05','1987-05-06',620.0);
insert into wyplaty values(4,6,'1986-03-25','1986-03-26',790.0);
insert into wyplaty values(4,11,'1987-07-04','1987-07-05',300.0);
insert into wyplaty values(4,25,'1985-05-23','1985-05-24',730.0);
insert into wyplaty values(4,106,'1988-03-04','1988-03-05',870.0);
insert into wyplaty values(4,106,'1988-06-04','1988-06-05',830.0);
insert into wyplaty values(5,2,'1990-03-16','1990-03-17',200.0);
insert into wyplaty values(5,3,'1990-09-16','1990-09-17',330.0);
insert into wyplaty values(5,13,'1985-01-23','1985-01-24',560.0);
insert into wyplaty values(5,18,'1986-07-23','1986-07-24',420.0);
insert into wyplaty values(5,18,'1986-09-23','1986-09-24',390.0);
insert into wyplaty values(5,103,'1987-07-12','1987-07-13',310.0);
insert into wyplaty values(5,106,'1988-03-04','1988-03-05',250.0);
insert into wyplaty values(6,5,'1987-05-05','1987-05-06',760.0);
insert into wyplaty values(6,8,'1992-05-09','1992-05-10',340.0);
insert into wyplaty values(6,8,'1992-07-09','1992-07-10',450.0);
insert into wyplaty values(6,10,'1983-12-26','1983-12-27',220.0);
insert into wyplaty values(6,17,'1987-09-02','1987-09-03',640.0);
insert into wyplaty values(6,21,'1983-11-05','1983-11-06',780.0);
insert into wyplaty values(6,24,'1986-09-21','1986-09-22',610.0);
insert into wyplaty values(7,39,'1988-04-09','1988-04-10',160.0);
insert into wyplaty values(7,39,'1988-06-09','1988-06-10',110.0);
insert into wyplaty values(8,6,'1986-03-25','1986-03-26',280.0);
insert into wyplaty values(8,8,'1992-05-09','1992-05-10',880.0);
insert into wyplaty values(8,13,'1985-01-23','1985-01-24',870.0);
insert into wyplaty values(8,14,'1992-07-08','1992-07-09',730.0);
insert into wyplaty values(8,16,'1984-02-24','1984-02-25',270.0);
insert into wyplaty values(8,22,'1984-04-24','1984-04-25',300.0);
insert into wyplaty values(8,28,'1984-06-23','1984-06-24',270.0);
insert into wyplaty values(9,13,'1985-01-23','1985-01-24',44230.0);
insert into wyplaty values(9,14,'1992-07-08','1992-07-09',540.0);
insert into wyplaty values(9,18,'1986-07-23','1986-07-24',200.0);
insert into wyplaty values(9,18,'1986-09-23','1986-09-24',420.0);
insert into wyplaty values(9,28,'1984-06-23','1984-06-24',330.0);
insert into wyplaty values(9,104,'1990-01-19','1990-01-20',830.0);
insert into wyplaty values(9,104,'1990-06-19','1990-06-20',390.0);
insert into wyplaty values(9,107,'1990-02-18','1990-02-19',250.0);
insert into wyplaty values(11,2,'1990-03-16','1990-03-17',390.0);
insert into wyplaty values(11,15,'1983-09-06','1983-09-07',540.0);
insert into wyplaty values(11,18,'1986-07-23','1986-07-24',440.0);
insert into wyplaty values(11,102,'1987-07-02','1987-07-03',790.0);
insert into wyplaty values(11,104,'1990-01-19','1990-01-20',830.0);
insert into wyplaty values(11,105,'1991-03-15','1991-03-16',250.0);
insert into wyplaty values(11,106,'1988-03-04','1988-03-05',330.0);
insert into wyplaty values(12,3,'1990-09-16','1990-09-17',7000.0);
insert into wyplaty values(12,3,'1990-11-16','1990-11-17',230.0);
insert into wyplaty values(12,4,'1983-04-05','1983-04-06',450.0);
insert into wyplaty values(12,20,'1992-09-06','1992-09-07',220.0);
insert into wyplaty values(12,20,'1992-11-06','1992-11-07',760.0);
insert into wyplaty values(12,31,'1985-04-16','1985-04-17',450.0);
insert into wyplaty values(12,40,'1987-02-28','1987-03-01',2230.0);
insert into wyplaty values(12,105,'1991-03-15','1991-03-16',590.0);
insert into wyplaty values(13,2,'1990-03-16',null,770.0);
insert into wyplaty values(13,10,'1983-12-26','1983-12-27',850.0);
insert into wyplaty values(13,17,'1987-09-02','1987-09-03',340.0);
insert into wyplaty values(13,21,'1983-11-05','1983-11-06',710.0);
insert into wyplaty values(13,22,'1984-04-24','1984-04-25',220.0);
insert into wyplaty values(13,28,'1984-06-23','1984-06-24',640.0);
insert into wyplaty values(13,104,'1990-01-19',null,560.0);
insert into wyplaty values(14,40,'1987-02-28','1987-03-01',2610.0);
insert into wyplaty values(15,6,'1986-03-25','1986-03-26',700.0);
insert into wyplaty values(15,6,'1986-07-25','1986-07-26',290.0);
insert into wyplaty values(15,15,'1983-09-06','1983-09-07',480.0);
insert into wyplaty values(15,20,'1992-09-06','1992-09-07',510.0);
insert into wyplaty values(15,22,'1984-04-24','1984-04-25',400.0);
insert into wyplaty values(15,31,'1985-04-16','1985-04-17',1000.0);
insert into wyplaty values(15,104,'1990-01-19','1990-01-20',580.0);
insert into wyplaty values(15,106,'1988-03-04','1988-03-05',810.0);
insert into wyplaty values(16,5,'1987-05-05','1987-05-06',280.0);
insert into wyplaty values(16,10,'1983-12-26','1983-12-27',610.0);
insert into wyplaty values(16,13,'1985-01-23','1985-01-24',880.0);
insert into wyplaty values(16,15,'1983-09-06','1983-09-07',590.0);
insert into wyplaty values(16,17,'1987-09-02','1987-09-03',270.0);
insert into wyplaty values(16,22,'1984-04-24','1984-04-25',250.0);
insert into wyplaty values(16,28,'1984-06-23','1984-06-24',230.0);
insert into wyplaty values(18,6,'1986-03-25','1986-03-26',300.0);
insert into wyplaty values(18,8,'1992-05-09','1992-05-10',880.0);
insert into wyplaty values(18,14,'1992-07-08','1992-07-09',270.0);
insert into wyplaty values(18,17,'1987-09-02','1987-09-03',250.0);
insert into wyplaty values(18,21,'1983-11-05','1983-11-06',230.0);
insert into wyplaty values(18,103,'1987-07-12','1987-07-13',280.0);
insert into wyplaty values(18,105,'1991-03-15','1991-03-16',270.0);
insert into wyplaty values(19,8,'1992-05-09','1992-05-10',440.0);
insert into wyplaty values(19,18,'1986-07-23','1986-07-24',870.0);
insert into wyplaty values(19,106,'1988-03-04','1988-03-05',540.0);
insert into wyplaty values(19,107,'1990-02-18','1990-02-19',620.0);
insert into wyplaty values(20,3,'1990-09-16','1990-09-17',480.0);
insert into wyplaty values(20,4,'1983-04-05','1983-04-06',770.0);
insert into wyplaty values(20,5,'1987-05-05','1987-05-06',810.0);
insert into wyplaty values(20,6,'1986-03-25','1986-03-26',580.0);
insert into wyplaty values(20,20,'1992-09-06','1992-09-07',400.0);
insert into wyplaty values(20,30,'1986-11-20','1986-11-21',250.0);
insert into wyplaty values(20,39,'1988-04-09','1988-04-10',230.0);
insert into wyplaty values(20,42,'2000-07-03','2000-07-04',50.0);
insert into wyplaty values(20,104,'1990-01-19','1990-01-20',710.0);
insert into wyplaty values(20,106,'1988-03-04','1988-03-05',290.0);
insert into wyplaty values(21,42,'2000-07-03','2000-07-04',40.0);
insert into wyplaty values(22,3,'1990-09-16','1990-09-17',300.0);
insert into wyplaty values(22,4,'1983-04-05','1983-04-06',790.0);
insert into wyplaty values(22,11,'1987-07-04','1987-07-05',880.0);
insert into wyplaty values(22,22,'1984-04-24','1984-04-25',730.0);
insert into wyplaty values(22,25,'1985-05-23','1985-05-24',620.0);
insert into wyplaty values(22,103,'1987-07-12','1987-07-13',870.0);
insert into wyplaty values(23,14,'1992-07-08','1992-07-09',700.0);
insert into wyplaty values(23,20,'1992-09-06','1992-09-07',490.0);
insert into wyplaty values(23,28,'1984-06-23','1984-06-24',320.0);
insert into wyplaty values(23,28,'1984-11-23','1984-11-24',460.0);
insert into wyplaty values(23,101,'1988-10-30','1988-10-31',290.0);
insert into wyplaty values(23,104,'1990-01-19','1990-01-20',510.0);
insert into wyplaty values(23,104,'1990-06-19','1990-06-20',260.0);
insert into wyplaty values(23,106,'1988-03-04','1988-03-05',400.0);
insert into wyplaty values(25,1,'1990-01-16','1990-01-17',850.0);
insert into wyplaty values(25,1,'1990-11-16','1990-11-17',760.0);
insert into wyplaty values(25,11,'1987-07-04','1987-07-05',450.0);
insert into wyplaty values(25,16,'1984-02-24','1984-02-25',640.0);
insert into wyplaty values(25,20,'1992-09-06','1992-09-07',340.0);
insert into wyplaty values(25,22,'1984-04-24','1984-04-25',710.0);
insert into wyplaty values(25,24,'1986-09-21','1986-09-22',220.0);
insert into wyplaty values(25,39,'1988-04-09','1988-04-10',270.0);
insert into wyplaty values(26,2,'1990-03-16','1990-03-17',560.0);
insert into wyplaty values(26,6,'1986-03-25','1986-03-26',700.0);
insert into wyplaty values(26,17,'1987-09-02','1987-09-03',490.0);
insert into wyplaty values(26,17,'1987-10-02','1987-10-03',460.0);
insert into wyplaty values(26,24,'1986-09-21','1986-09-22',600.0);
insert into wyplaty values(26,103,'1987-07-12','1987-07-13',320.0);
insert into wyplaty values(26,106,'1988-03-04','1988-03-05',260.0);
insert into wyplaty values(27,4,'1983-04-05','1983-04-06',810.0);
insert into wyplaty values(27,15,'1983-09-06','1983-09-07',710.0);
insert into wyplaty values(27,15,'1983-11-06','1983-11-07',640.0);
insert into wyplaty values(27,18,'1986-07-23','1986-07-24',580.0);
insert into wyplaty values(27,22,'1984-04-24','1984-04-25',770.0);
insert into wyplaty values(27,24,'1986-09-21','1986-09-22',480.0);
insert into wyplaty values(27,25,'1985-05-23','1985-05-24',850.0);
insert into wyplaty values(29,6,'1986-03-25','1986-03-26',250.0);
insert into wyplaty values(29,8,'1992-05-09','1992-05-10',780.0);
insert into wyplaty values(29,16,'1984-02-24','1984-02-25',610.0);
insert into wyplaty values(29,24,'1986-09-21','1986-09-22',590.0);
insert into wyplaty values(29,25,'1985-05-23','1985-05-24',760.0);
insert into wyplaty values(29,102,'1987-07-02','1987-07-03',270.0);
insert into wyplaty values(29,104,'1990-01-19','1990-01-20',230.0);
insert into wyplaty values(30,4,'1983-04-05','1983-04-06',490.0);
insert into wyplaty values(30,10,'1983-12-26','1983-12-27',290.0);
insert into wyplaty values(30,22,'1984-04-24','1984-04-25',480.0);
insert into wyplaty values(30,24,'1986-09-21','1986-09-22',700.0);
insert into wyplaty values(30,24,'1986-10-21','1986-10-22',320.0);
insert into wyplaty values(30,40,'1987-02-28','1987-03-01',3240.0);
insert into wyplaty values(30,40,'1987-04-28','1987-04-29',1760.0);
insert into wyplaty values(30,104,'1990-01-19','1990-01-20',510.0);
insert into wyplaty values(30,105,'1991-03-15','1991-03-16',400.0);
insert into wyplaty values(30,105,'1991-06-15','1991-06-16',810.0);
insert into wyplaty values(31,2,'1990-03-16','1990-03-17',500.0);
insert into wyplaty values(31,17,'1987-09-02','1987-09-03',2340.0);
insert into wyplaty values(31,31,'1985-04-16','1985-04-17',1000.0);
insert into wyplaty values(31,100,'1985-01-16','1985-01-17',56700.0);
insert into wyplaty values(32,10,'1983-12-26','1983-12-27',800.0);
insert into wyplaty values(32,22,'1984-04-24','1984-04-25',55555.0);
insert into wyplaty values(33,1,'1990-01-16','1990-01-17',880.0);
insert into wyplaty values(33,8,'1992-05-09','1992-05-10',1000.0);
insert into wyplaty values(33,18,'1986-07-23','1986-07-24',500.0);
insert into wyplaty values(34,22,'1984-04-24','1984-04-25',3000.0);
insert into wyplaty values(34,30,'1986-11-20','1986-11-21',900.0);
insert into wyplaty values(35,10,'1983-12-26','1983-12-27',6700.0);
insert into wyplaty values(36,5,'1987-05-05','1987-05-06',340.0);
insert into wyplaty values(36,10,'1983-12-26','1983-12-27',460.0);
insert into wyplaty values(36,40,'1987-02-28','1987-03-01',760.0);
insert into wyplaty values(36,40,'1987-05-28','1987-05-29',610.0);
insert into wyplaty values(37,10,'1983-12-26','1983-12-27',600.0);
insert into wyplaty values(37,40,'1987-02-28','1987-03-01',230.0);
insert into wyplaty values(37,101,'1988-10-30','1988-10-31',270.0);
insert into wyplaty values(40,31,'1985-04-16','1985-04-17',5.0);
insert into wyplaty values(41,31,'1985-04-16','1985-04-17',4.0);
insert into zespoly values(1,'OPROGRAMOWANIE',1,1);
insert into zespoly values(2,'TEORIA INFORMATYKI',2,1);
insert into zespoly values(3,'SIECI KOMPUTEROWE ',3,1);
insert into zespoly values(4,'BUDOWA',4,1);
insert into zespoly values(5,'AUTOMATY',25,3);
insert into zespoly values(6,'BUDOWA KOPARKI',10,5);
insert into zespoly values(7,'PIECE ',21,6);
insert into zespoly values(8,'WEGIEL',29,5);
insert into zespoly values(9,'KONSTRUKCJA ',33,5);
insert into zespoly values(10,'LIKWIDACJA',21,6);
insert into zespoly values(11,'POZIOMZEROWY',37,5);
SELECT nazwisko, COUNT(DISTINCT r.nr_przedm)
FROM (pracownicy p
JOIN przedmioty pr ON p.nr_prac=pr.NR_ODP_PRAC)
JOIN rozklady r ON r.nr_prac=p.nr_prac
GROUP BY nazwisko, p.nr_prac;
Create Table Books
(ISBN varchar(10) Primary key,
Title varchar(80),
Author varchar(60),
Publisher varchar(60),
PubDate date,
Lang varchar(20),
Category varchar(25),
PaperB varchar(1),
PBsalePrice decimal(5,2),
DigitF varchar(1),
DigsalePrice decimal(5,2),
NumPages integer);
Insert Into Books
values(6071133483,'Ser Feliz Era Esto','Eduardo Sacheri', 'Alfaguara', '2015/3/17', 'Spanish','Fiction','P',15.25, 'D', 7.21, 248);
Insert Into Books
values(6071127661,'La vida que Pensamos','Eduardo Sacheri', 'Alfaguara','2014/1/30','Spanish','Fiction','P',18.28, null, null, 540);
Insert Into Books
values('843231255X','El futbol a sol y sombra', 'Eduardo Galeano','Siglo XXI', '2006/1/11', 'Spanish','Fiction','P',16.95,null, null, 414);
Insert Into Books
values(8432311456,'Las venas abiertas de America Latina', 'Eduardo Galeano','Siglo XXI', '2006/1/11', 'Spanish','Fiction','P',24.48,'D',7.05,380);
Insert Into Books
values(0307474720,'Cien años de Soledad','Gabriel Garcia Marquez','Vintage Español','2009/9/22','Spanish','Fiction','P',10.15,'D',10.57,496);
Insert Into Books
values(0307387267,'El amor en los tiempos del colera','Gabriel Garcia Marquez','Vintage Español','2007/10/9','Spanish','Fiction','P',12.78,'D',11.38,464);
Insert Into Books
values(0062511408,'El Alquimista', 'Paulo Coelho', 'Rayo','2002/1/22', 'Spanish','Fiction','P',8.46,'D',5.03,192);
Insert Into Books
values('607429223X', 'Once minutos','Paulo Coelho','Debolsillo','2012/8/10','Spanish','Fiction', 'P',8.46,null,null ,296);
Insert Into Books
values(0061829684, 'El vencedor esta solo','Paulo Coelho', 'Rayo','2010/8/31', 'Spanish','Fiction',null,null,'D',9.89,416);
Insert Into Books
values(0060883286,'One Hundred Years of Solitude','Gabriel Garcia Marquez','Harper Perennial Modern Classics','2009/8/31','English','Fiction','P',9.57,null,null,417);
Insert Into Books
values(0062315005,'The Alchemist', 'Paulo Coelho', 'HarperOne','2014/4/15', 'English','Fiction','P',16.98,'D',9.67,208);
Insert Into Books
values(0853459916,'Open Veins of Latin America','Eduardo Galeano',' Monthly Review Press','1997/11/25','English','Politics','P',12.52,'D',7.05,317);
Insert Into Books
values('073607483X','Periodization Training:Theory and Methodology','Tudor Bompa','Human Kinetics', '2009/6/22', 'English','Sports','P',66.35,null, null, 424);
Insert Into Books
values(0736058036,'Essentials of strength training','Thomas R. Baechle & Roger W. Earle','Human Kinetics', '2008/6/2', 'English','Sports','P',42.41,null,null,656);
Insert Into Books
values(0736092269,'Strength Training Anatomy','Frédéric Delavier','Human Kinetics', '2010/3/9', 'English','Sports','P',11.35,null, null,192);
Insert Into Books
values(1450413994,'Core Training Anatomy','Frédéric Delavier','Human Kinetics', '2011/10/10', 'English','Sports',null,null,'D',14.92,144);
Insert Into Books
values(1591640253,'Soccer modern tactics','Alessandro Zauli','Reedswain Books & Videos','2002/8/1','English','Coaching',null,null,'D',7.03,128);
Insert Into Books
values(1890946737,'Teambuilding: the road to success','Rinus Michels','Reedswain Books & Videos','2002/8/1','English','Coaching','P',16.12,null,null,218);
Insert Into Books
values(1890946710, 'Attacking Soccer','Massimo Lucchesi','Reedswain Books & Videos','2001/12/1','English','Coaching',null,null,'D',7.19,224);
Insert Into Books
values(1890946036,'The Coaching Philosophies of Louis van Gaal and the Ajax Coaches','Henny Kormelink','Reedswain Books & Videos','2001/12/1','English','Coaching','P',12.65,'D',7.19,224);
Insert Into Books
values(9896551979,'Jose Mourinho: Special Leadership','Luis Lourenco','Prime Books','2014/4/18','English','Sports','P',9.78,'D',7.15,164);
Insert Into Books
values(1408843501,'The Manager','Mike Carson','Bloomsbury USA','2014/10/28','English','Sports','P',12.42,'D',7.05,320);
Insert Into Books
values(1409129462,'Pep Guardiola: Another Way of Winning', 'Guillem Balague','Orion Publishing','2013/12/1','English','Sports','P',17.06,'D',10.23,362);
Insert Into Books
values(1909430161,'I Think Therefore I Play','Andrea Pirlo','BackPage Press','2015/10/1','English','Sports','P',11.21,'D',5.64,200);
Insert Into Books
values(1472224272,'Luis Suarez: Crossing the Line - My Story','Luis Suarez','Headline Book Publishing','2015/8/1','English','Sports','P',16.95,null,null,288);
Insert Into Books
values(1938591097,'The Flea - The Amazing Story of Leo Messi','Michael Part','Sole Books','2013/8/1','English','Sports','P',8.99,'D',4.24,160);
Insert Into Books
values(1845967100,'Red Men: Liverpool Football Club The Biography','John Williams',' Mainstream Publishing','2011/11/28','English','Sports','P',18.40,'D',14.08,317);
Insert Into Books
values('147221398X','Steven Gerrard: My Liverpool Story','Steven Gerrard','Headline Book Publishing','2015/4/1','English','Sports','P',18.68,'D',17.54,304);
Insert Into Books
values(1780228821,'The Second Half','Roddy Doyle','Orion Publishing','2015/9/15','English','Sports','P',11.99,'D',6.47,320);
Insert Into Books
values(0718193997,'Keane: The Autobiography','Roy Keane', 'Penguin Global','2012/1/18','English','Sports','P',11.99,null,null,336);
Select title
FROM books;
select title,author
from books;
/* 1 SQL CREATE TABLE */
/* student table */
CREATE TABLE student
(student_number CHAR(8)
, surname CHAR VARYNG(50)
, forename CHAR VARYING(50)
, gender CHAR VARYING(7)
, title CHAR VARYING(4)
, date_of_birth DATE
, department_number CHAR (6)
, course_number CHAR (10)
);
/* Staff Table */
CREATE TABLE staff
(staff_number CHAR(10)
, surname CHAR VARYING(50)
, forename CHAR VARYING(50)
, gender CHAR VARYING(7)
, title CHAR VARYING(4)
, date_of_birth DATE
, salary DECIMAL(7,2)
, dept_number CHAR(6)
);
/* Department Table */
CREATE TABLE department
(department_number CHAR(6)
, department_name CHAR VARYING(50)
);
/* Course Table */
CREATE TABLE course
(course_number CHAR(10)
, course_name CHAR VARYING(50)
, department_number CHAR(6)
);
/* Unit Table */
CREATE TABLE unit
(unit_number CHAR(10)
, unit_name CHAR VARYING(50)
, course_number CHAR(10)
);
/* Altering student table */
ALTER TABLE student ADD telephone_number CHAR(7);
ALTER TABLE student ADD mobile_number CHAR(10);
/* Altering staff table */
ALTER TABLE staff ADD email CHAR VARYING(50);
/* Altering department table */
ALTER TABLE department ADD location CHAR VARYING(50);
/* inputing records into student table */
INSERT INTO student
VALUES ('SN002349', 'Grant', 'Richard', 'Male', 'Mr.', '1995-09-19', 'DEP22', 'BS0101',
'913-515-7889', '913-545-7898');
INSERT INTO student (student_number, surname, forename, gender, title,
department_number, course_number)
VALUES ('SN003211', 'Huber', 'Jennifer', 'Female', 'Ms.', 'DEP22', 'BS0101');
INSERT INTO student
VALUES ('SN001388', 'Ramirez', 'Marco', 'Male', 'Mr.', '1994-01-20', 'DEP21', 'IS8100',
'613-415-2829', '616-535-6867');
SELECT * FROM student
PRAGMA foreign_keys = ON;
/* Create a table called NAMES */
CREATE TABLE A(id int PRIMARY KEY, name string);
CREATE TABLE B(
id int PRIMARY KEY,
id2 int,
FOREIGN KEY (id2) REFERENCES A(id) ON UPDATE CASCADE ON DELETE CASCADE
);
/* Create few records in this table */
INSERT INTO A VALUES(1, 'A');
INSERT INTO B VALUES(2, 1);
INSERT INTO B VALUES(3, 1);
INSERT OR REPLACE INTO A VALUES(1, 'A');
/* Display all the records from the table */
SELECT * FROM B;
BEGIN TRANSACTION;
/* Create a table called NAMES */
CREATE TABLE COMPANY
(CName VARCHAR(15) NOT NULL,
Main_Address VARCHAR(30) NOT NULL,
Location VARCHAR(15),
Manager VARCHAR(15) NOT NULL,
Pri_Cont_Person VARCHAR(15),
Telephone CHAR(10),
Fac_BranchID CHAR(10) NOT NULL,
Off_BranchID CHAR(10) NOT NULL,
War_BranchID CHAR(10) NOT NULL,
PRIMARY KEY(CName, Main_Address, Fac_BranchID,Off_BranchID,War_BranchID));
CREATE TABLE CUSTOMER
(Name VARCHAR(15) NOT NULL,
Address VARCHAR(30),
Ship VARCHAR(30) NOT NULL,
Bill VARCHAR(30) NOT NULL,
Tax DECIMAL(10,2),
Discount INT CHECK(Discount < 100) DEFAULT 0,
Fax CHAR(10),
Phone CHAR(10),
Pri_Phone CHAR(10),
Email VARCHAR(15),
Pri_Email VARCHAR(15),
Company VARCHAR(15),
PRIMARY KEY(Name),
FOREIGN KEY(Address) REFERENCES ADDRESS(Cust_Address),
FOREIGN KEY(Fax) REFERENCES FAX(Cust_Fax),
FOREIGN KEY(Phone) REFERENCES PHONE(Cust_Phone),
FOREIGN KEY(Email) REFERENCES EMAIL(Cust_Email),
FOREIGN KEY(Company) REFERENCES COMPANY(CName));
CREATE TABLE MANUFACTURES
(Name VARCHAR(15) NOT NULL,
Email VARCHAR(15),
Pri_Email VARCHAR(15),
Phone CHAR(10),
Pri_Phone CHAR(10),
Fax CHAR(10),
Address VARCHAR(30),
Ship VARCHAR(30) NOT NULL,
Bill VARCHAR(30) NOT NULL,
PRIMARY KEY(Name),
FOREIGN KEY(Address) REFERENCES ADDRESS(MANU_Address),
FOREIGN KEY(Fax) REFERENCES FAX(MANU_Fax),
FOREIGN KEY(Phone) REFERENCES PHONE(MANU_Phone),
FOREIGN KEY(Email) REFERENCES EMAIL(MANU_Email));
CREATE TABLE PART
(ID CHAR(10) NOT NULL,
Cost DECIMAL(10,2) CHECK(Cost > 0),
Manufactures VARCHAR(15),
Warehouse VARCHAR(15),
PRIMARY KEY(ID),
FOREIGN KEY(Manufactures) REFERENCES MANUFACTURES(Name),
FOREIGN KEY(Warehouse) REFERENCES WAREHOUSE(Name));
CREATE TABLE WAREHOUSE
(Name VARCHAR(15) NOT NULL,
ID CHAR(10) NOT NULL,
Inventory INT CHECK(Inventory > 0) DEFAULT 0,
Branch_ID CHAR(10),
PRIMARY KEY(Name, ID)
FOREIGN KEY(Branch_ID) REFERENCES COMPANY(War_BranchID));
CREATE TABLE FACTORY
(ID CHAR(10) NOT NULL,
Branch_ID CHAR(10),
PRIMARY KEY(ID)
FOREIGN KEY(Branch_ID) REFERENCES COMPANY(Fac_BranchID));
CREATE TABLE OFFICE
(ID CHAR(10) NOT NULL,
Branch_ID CHAR(10),
PRIMARY KEY(ID)
FOREIGN KEY(Branch_ID) REFERENCES COMPANY(Off_BranchID));
CREATE TABLE PRODUCTS
(ID CHAR(10) NOT NULL,
Price DECIMAL(10,2) CHECK(Price > 0),
Routing_Sheet VARCHAR(30),
BOM VARCHAR(15),
PRIMARY KEY(ID),
FOREIGN KEY(Routing_Sheet) REFERENCES ROUNTING_SHEET(ID),
FOREIGN KEY(BOM) REFERENCES BOM(ID));
CREATE TABLE ROUNTING_SHEET
(ID CHAR(10) NOT NULL,
Stage VARCHAR(15) DEFAULT 0,
Start_Time DATE,
End_Time DATE CHECK(End_Time >= Start_Time),
Expected_Time DATE DEFAULT 0,
PRIMARY KEY(ID));
CREATE TABLE BOM
(ID CHAR(10) NOT NULL,
Part_BOM VARCHAR(15),
PRIMARY KEY(ID));
CREATE TABLE ORDER_BY
(Order_by CHAR(10) NOT NULL,
Reason VARCHAR(30) NOT NULL,
Approve_Deny CHAR DEFAULT ‘T’,
Invoice VARCHAR(30),
Price INT,
PRIMARY KEY(Order_by),
FOREIGN KEY(Invoice) REFERENCES INVOICE(ID));
CREATE TABLE INVOICE
(ID CHAR(10) NOT NULL,
Ship_Date DATE,
Price DECIMAL(10,2),
Due_Date DATE CHECK(Ship_Date < Due_Date),
Product VARCHAR(15),
Quality VARCHAR(15),
Discount DECIMAL(2,1) CHECK (Discount < 100) DEFAULT 0,
Sale_tax DECIMAL(10,2) CHECK (Sale_tax > 0),
PRIMARY KEY(ID));
CREATE TABLE EMPLOYEE
(ID CHAR(10) NOT NULL,
Organized_Info VARCHAR(30),
Tax_Info VARCHAR(30),
Benefits VARCHAR(30),
SSN CHAR(10),
Gender CHAR,
FName VARCHAR(15),
LName VARCHAR(15),
Mobile_Phone CHAR(10),
Home_Phone CHAR(10),
Birth DATE,
Type VARCHAR(15),
Company VARCHAR(15),
Age INT,
PRIMARY KEY(ID),
FOREIGN KEY(Company) REFERENCES COMPANY(CName));
CREATE TABLE OFFICER
(ID CHAR(10) NOT NULL,
Salary DECIMAL(10,2) CHECK(Salary >= 0),
PRIMARY KEY(ID));
CREATE TABLE WORKER
(ID CHAR(10) NOT NULL,
Hour INT DEFAULT 0,
Salary_per_Hour DECIMAL(10,2) CHECK(Salary_per_Hour > 0),
PRIMARY KEY(ID));
CREATE TABLE SALES
(ID CHAR(10) NOT NULL,
Amount_of_Sales INT CHECK(Amount_of_Sales > 0) DEFAULT 0,
Commision DECIMAL(10,2) DEFAULT 0,
PRIMARY KEY(ID));
CREATE TABLE SHOP
(ID CHAR(10) NOT NULL,
Name VARCHAR(15) NOT NULL,
Order_by CHAR(10) NOT NULL,
PRIMARY KEY(ID, Name, Order_by),
FOREIGN KEY(ID) REFERENCES PRODUCTS(ID),
FOREIGN KEY(Name) REFERENCES COMPANY(CName),
FOREIGN KEY(Order_by) REFERENCES ORDER_BY(Order_by));
CREATE TABLE ASSEMBLE
(Part_ID CHAR(10) NOT NULL,
Prod_ID CHAR(10) NOT NULL,
PRIMARY KEY(Part_ID, Prod_ID),
FOREIGN KEY(Part_ID) REFERENCES MANUFACTURES(ID),
FOREIGN KEY(Prod_ID) REFERENCES PRODUCTS(ID));
CREATE TABLE PHONE
(Cust_Phone CHAR(10) NOT NULL,
Manu_Phone CHAR(10) NOT NULL,
ID CHAR(9) NOT NULL,
PRIMARY KEY(Cust_Phone, Manu_Phone ));
CREATE TABLE EMAIL
(Cust_Email CHAR(10) NOT NULL,
Manu_Email CHAR(10) NOT NULL,
ID CHAR(9) NOT NULL,
PRIMARY KEY(Cust_Email, Manu_Email));
CREATE TABLE FAX
(Cust_Fax CHAR(10) NOT NULL,
Manu_Fax CHAR(10) NOT NULL,
ID CHAR(9) NOT NULL,
PRIMARY KEY(Cust_Fax, Manu_Fax));
CREATE TABLE ADDRESS
(Cust_Address VARCHAR(30) NOT NULL,
Manu_Address VARCHAR(30) NOT NULL,
ID CHAR(9) NOT NULL,
PRIMARY KEY(Cust_Address, Manu_Address));
CREATE TABLE PURCHASE
(Main_Address VARCHAR(30) NOT NULL,
Name VARCHAR(30) NOT NULL,
FOREIGN KEY(Main_Address) REFERENCES COMPANY(CName),
FOREIGN KEY(Name) REFERENCES MANUFACTURES(Name));
INSERT INTO COMPANY VALUES('MetLife Inc','80 West Lane Ave','Columbus','Mike', 'Jack', '1234567890','1112223334','1231231231','4564564566');
/* Display all the records from the table */
SELECT * FROM COMPANY;
BEGIN TRANSACTION; /* Create a table called NAMES */ CREATE TABLE NAMES(Id integer PRIMARY KEY, Name text); /* Create few records in this table */ INSERT INTO NAMES VALUES(1,'Tom'); INSERT INTO NAMES VALUES(2,'Lucy'); INSERT INTO NAMES VALUES(3,'Frank'); INSERT INTO NAMES VALUES(4,'Jane'); INSERT INTO NAMES VALUES(5,'Robert'); COMMIT; /* Display all the records from the table */ SELECT * FROM NAMES;
We use cookies to provide and improve our services. By using our site, you consent to our Cookies Policy. Accept Learn more
