Jump to content
riscanu1

Baza de date sql

By riscanu1, in Programare

Recommended Posts

u0m3 Newbie

u0m3

Posted
Posted (edited)

@riscanu1 Poti dovedi ca ti-ai dat macar interesul?

Uite, eu am "postat" aici rezolvarea (cu tot cu un sample data, modelat oarecum dupa tabelele din poza), dar am codificat raspunsul. Daca poti dovedi, macar ca ai incercat, ca ti-ai dat interesul, am sa dau si parametrii AES folositi la codificare.

 

L.E.: Am sa las totusi rezolvarea... (Key = Sarmisegetuza012 | IV = 31337$cript )

Spoiler 

-- begin DROP TABLES region
DROP TABLE IF EXISTS "PERSOANE";
DROP TABLE IF EXISTS "STUD_SEM_LABOR";
DROP TABLE IF EXISTS "STUDENTI";
DROP TABLE IF EXISTS "DISCIPLINE";
DROP TABLE IF EXISTS "PROFI";
DROP TABLE IF EXISTS "EXAMENE";
DROP TABLE IF EXISTS "FORMATII_CURS";
DROP TABLE IF EXISTS "SPECIALIZARI";
DROP TABLE IF EXISTS "TRASEU_STUD";
DROP TABLE IF EXISTS "PLAN_INVATAMANT";
DROP TABLE IF EXISTS "FORMATII_SEMINAR_LABORATOR";
DROP TABLE IF EXISTS "STUD_CURSURI";
DROP TABLE IF EXISTS "STUD_SEM_LABOR";
-- end DROP TABLES region

-- CREATE TABLES region
CREATE TABLE IF NOT EXISTS "PERSOANE" (
  "IdPers" INTEGER,
  "NumePren" TEXT,
  "Loc" TEXT,
  "Jud" TEXT,
  "Tel" TEXT,
  "EMail" TEXT,
  CONSTRAINT "CPK_IdPers"
    PRIMARY KEY ("IdPers")
);

CREATE TABLE IF NOT EXISTS "STUDENTI" (
  "Matricol" TEXT,
  "IdPers" INTEGER,
  "AnInmatr" INTEGER,
  "SituatCrta" TEXT,
  "AnAbsolv" INTEGER,
  CONSTRAINT "CPK_Matricol"
    PRIMARY KEY ("Matricol"),
  CONSTRAINT "CFK_IdPers"
    FOREIGN KEY ("IdPers") REFERENCES "PERSOANE" ("IdPers")
      ON DELETE CASCADE ON UPDATE NO ACTION
);

CREATE TABLE IF NOT EXISTS "DISCIPLINE" (
  "CodDisc" TEXT,
  "DenDisc" TEXT,
  "NrCredite" INTEGER,
  "NrOreCurs" INTEGER,
  "NrOreSmLb" INTEGER,
  CONSTRAINT "CPK_CodDisc"
    PRIMARY KEY ("CodDisc")
);

CREATE TABLE IF NOT EXISTS "PROFI" (
  "CodProf" INTEGER,
  "NumeProf" TEXT,
  "Birou" TEXT,
  "Catedra" TEXT,
  "EMail" TEXT,
  CONSTRAINT "CPK_CodProf"
    PRIMARY KEY ("CodProf")
);

CREATE TABLE IF NOT EXISTS "EXAMENE" (
  "Matricol" TEXT,
  "CodDisc" TEXT,
  "DataEx" TEXT,
  "Nota" REAL,
  CONSTRAINT "CPK_Matricol_CodDisc_DataEx"
    PRIMARY KEY ("Matricol", "CodDisc", "DataEx"),
  CONSTRAINT "CFK_Matricol"
    FOREIGN KEY ("Matricol") REFERENCES "STUDENTI" ("Matricol")
      ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT "CFK_CodDisc"
    FOREIGN KEY ("CodDisc") REFERENCES "DISCIPLINE" ("CodDisc")
      ON DELETE CASCADE ON UPDATE NO ACTION
);

CREATE TABLE IF NOT EXISTS "SPECIALIZARI" (
  "CodSpec" TEXT,
  "NumeSpec" TEXT,
  "CicluStudii" TEXT,
  CONSTRAINT "CPK_CodSpec"
    PRIMARY KEY ("CodSpec")
);

CREATE TABLE IF NOT EXISTS "TRASEU_STUD" (
  "AnUniv" TEXT,
  "Matricol" TEXT,
  "An" INTEGER,
  "CodSpec" TEXT,
  "Fstudii" TEXT,
  CONSTRAINT "CPK_AnUniv_Matricol"
    PRIMARY KEY ("AnUniv", "Matricol"),
  CONSTRAINT "CFK_Matricol"
    FOREIGN KEY ("Matricol") REFERENCES "STUDENTI" ("Matricol")
      ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT "CFK_CodSpec"
    FOREIGN KEY ("CodSpec") REFERENCES "SPECIALIZARI" ("CodSpec")
      ON DELETE CASCADE ON UPDATE NO ACTION
);

CREATE TABLE IF NOT EXISTS "PLAN_INVATAMANT" (
  "AnUniv" TEXT,
  "CodSpec" TEXT,
  "An" INTEGER,
  "CodDisc" TEXT,
  "Tip_Disc" TEXT,
  CONSTRAINT "CPK_AnUniv_CodSpec_An_CodDisc"
    PRIMARY KEY ("AnUniv", "CodSpec", "An", "CodDisc"),
  CONSTRAINT "CFK_CodSpec"
    FOREIGN KEY ("CodSpec") REFERENCES "SPECIALIZARI" ("CodSpec")
      ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT "CFK_CodDisc"
    FOREIGN KEY ("CodDisc") REFERENCES "DISCIPLINE" ("CodDisc")
      ON DELETE CASCADE ON UPDATE NO ACTION
);

CREATE TABLE IF NOT EXISTS "FORMATII_CURS" (
  "IdFC" INTEGER,
  "AnUniv" TEXT,
  "Semestru" INTEGER,
  "CodDisc" TEXT,
  "CodProf" INTEGER,
  CONSTRAINT "CPK_IdFC"
    PRIMARY KEY ("IdFC"),
  CONSTRAINT "CFK_CodProf"
    FOREIGN KEY ("CodProf") REFERENCES "PROFI" ("CodProf")
      ON DELETE CASCADE ON UPDATE NO ACTION
);

CREATE TABLE IF NOT EXISTS "FORMATII_SEMINAR_LABORATOR" (
  "IdFSL" INTEGER,
  "AnUniv" TEXT,
  "Semestru" INTEGER,
  "CodDisc" TEXT,
  "CodGrupa" TEXT,
  "CodProf" INTEGER,
  CONSTRAINT "CPK_IdFSL"
    PRIMARY KEY ("IdFSL"),
  CONSTRAINT "CFK_CodDisc"
    FOREIGN KEY ("CodDisc") REFERENCES "DISCIPLINE" ("CodDisc")
      ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT "CFK_CodProf"
    FOREIGN KEY ("CodProf") REFERENCES "PROFI" ("CodProf")
      ON DELETE CASCADE ON UPDATE NO ACTION
);

CREATE TABLE IF NOT EXISTS "STUD_CURSURI"
(
  "IdFC" INTEGER,
  "Matricol" TEXT,
  CONSTRAINT "CPK_IdFC_Matricol"
    PRIMARY KEY ("IdFC", "Matricol"),
  CONSTRAINT "CFK_IdFC"
    FOREIGN KEY ("IdFC") REFERENCES "FORMATII_CURS" ("IdFC")
      ON DELETE CASCADE ON DELETE NO ACTION,
  CONSTRAINT "CFK_IdFC"
    FOREIGN KEY ("Matricol") REFERENCES "STUDENTI" ("Matricol")
      ON DELETE CASCADE ON DELETE NO ACTION
);

CREATE TABLE IF NOT EXISTS "STUD_SEM_LABOR"
(
  "IdFSL" INTEGER,
  "Matricol" TEXT,
  CONSTRAINT "CPK_IdFC_Matricol"
    PRIMARY KEY ("IdFSL", "Matricol"),
  CONSTRAINT "CFK_IdFC"
    FOREIGN KEY ("IdFSL") REFERENCES "FORMATII_SEMINAR_LABORATOR" ("IdFSL")
      ON DELETE CASCADE ON DELETE NO ACTION,
  CONSTRAINT "CFK_IdFC"
    FOREIGN KEY ("Matricol") REFERENCES "STUDENTI" ("Matricol")
      ON DELETE CASCADE ON DELETE NO ACTION
);
-- end CREATE TABLE region

-- begin DATA INSERT region
INSERT INTO PERSOANE (IdPers, NumePren, Loc, Jud, Tel, EMail)
VALUES (12345, 'Buzu T Vasile', 'Focuri', 'Iasi', '076543210', 'vsy@k.ro'),
       (32232, 'Popescu W.T. Vasile', 'Manoaia', 'Piatra Neamt', '3223210', 'qbert@oldies.ro'),
       (20888, 'Bahrim R Ioana', 'Macon', 'Georgia(GA)', '478-775-9513', 'bahrim.ri@intake.ro'),
       (21000, 'Peles Curcan', 'Cucuieti Din Deal', 'Bacau', '578667667', 'peles.c@imag.ro'),
       (22000, 'Gheorghitoaia Mona-Lisa', 'Cucuietii Din Vale', 'Bacau', '578998998', 'gheorghitoaia.ml@imag.ro');

INSERT INTO STUDENTI (Matricol, IdPers, AnInmatr, SituatCrta, AnAbsolv)
VALUES ('IL13023', 12345, 2010, 'Absolvent', 2013),
       ('IL19240', 32232, 2010, 'Absolvent', 2013),
       ('IL20888', 20888, 2015, 'Absolvent', 2018),
       ('IL21000', 21000, 2010, 'Repetent', NULL),
       ('IL22000', 22000, 2010, 'Repetent', NULL);

INSERT INTO DISCIPLINE (CodDisc, DenDisc, NrCredite, NrOreCurs, NrOreSmLb)
VALUES ('AI2401', 'Baze de date', 6, 28, 28),
       ('AI2333', 'Microeconomie', 6, 28, 28);

INSERT INTO PROFI (CodProf, NumeProf, Birou, Catedra, EMail)
VALUES (11, 'Bratu Vasile', 'B301', 'InfoEc', 'br@a.ro');

INSERT INTO EXAMENE (Matricol, CodDisc, DataEx, Nota)
VALUES ('IL13001', 'AI2401', '2012-06-03', 4),
       ('IL21000', 'AI2333', '2019-03-01', 2),
       ('IL21000', 'AI2401', '2019-03-01', 2),
       ('IL22000', 'AI2333', '2019-03-01', 2),
       ('IL22000', 'AI2401', '2019-03-01', 2),
       ('IL20888', 'AI2401', '2019-03-01', 3);

INSERT INTO SPECIALIZARI (CodSpec, NumeSpec, CicluStudii)
VALUES ('IE', 'Info Economica', 'licenta');

INSERT INTO TRASEU_STUD (AnUniv, Matricol, An, CodSpec, Fstudii)
VALUES ('2011-2012', 'IL13023', 2, 'IE', 'zi'),
       ('2012-2013', 'IL19240', 3, 'IE', 'zi'),
       ('2018-2019', 'IL21000', 3, 'IE', 'zi'),
       ('2018-2019', 'IL22000', 3, 'IE', 'zi');

INSERT INTO PLAN_INVATAMANT (AnUniv, CodSpec, An, CodDisc, Tip_Disc)
VALUES ('2011-2012', 'SPE', 3, 'AI2401', 'Obligatorie'),
       ('2014-2015', 'CIG', 2, 'AI2401', 'Optionala');

INSERT INTO FORMATII_CURS (IdFC, AnUniv, Semestru, CodDisc, CodProf)
VALUES (111111, '2016-2017', 2, 'AI2401', 11);

INSERT INTO FORMATII_SEMINAR_LABORATOR (IdFSL, AnUniv, Semestru, CodDisc, CodGrupa, CodProf)
VALUES (661111, '2016-2017', 2, 'AI2401', 'IE21', 11);

INSERT INTO STUD_CURSURI (IdFC, Matricol)
VALUES (111111, 'IL13023'),
       (111111, 'IL20888'),
       (111111, 'IL21000'),
       (111111, 'IL22000');

INSERT INTO STUD_SEM_LABOR (IdFSL, Matricol)
VALUES (661111, 'IL13023'),
       (661111, 'IL20888'),
       (111111, 'IL21000'),
       (111111, 'IL22000');
-- end DATA INSERT region

-- begin QUESTIONS region
-- 1. La ce specializare si in ce an de studii se afla studentul 'Popescu W.T. Vasile' in 2012-2013?
SELECT Sp.NumeSpec as 'Specializare', TS.An
FROM SPECIALIZARI Sp
INNER JOIN TRASEU_STUD TS on Sp.CodSpec = TS.CodSpec
WHERE TS.Matricol IN (
  SELECT St.Matricol
  FROM STUDENTI St
  INNER JOIN PERSOANE P on St.IdPers = P.IdPers
  WHERE P.NumePren = 'Popescu W.T. Vasile'
);

-- 2. Cu ce profesori a facut cursuri studenta 'Bahrim R Ioana'?
SELECT DISTINCT Pr.NumeProf as 'Profesor'
FROM PROFI Pr
INNER JOIN FORMATII_CURS FC on Pr.CodProf = FC.CodProf
WHERE FC.IdFC IN (
  SELECT DISTINCT SC.IdFC
  FROM STUD_CURSURI SC
  INNER JOIN STUDENTI St on SC.Matricol = St.Matricol
  INNER JOIN PERSOANE P on St.IdPers = P.IdPers
  WHERE P.NumePren = 'Bahrim R Ioana'
)
UNION
SELECT DISTINCT Pr.NumeProf as 'Profesor'
FROM PROFI Pr
INNER JOIN FORMATII_SEMINAR_LABORATOR FSL on Pr.CodProf = FSL.CodProf
WHERE FSL.IdFSL IN (
  SELECT DISTINCT SSL.IdFSL
  FROM STUD_SEM_LABOR SSL
  INNER JOIN STUDENTI St on SSL.Matricol = St.Matricol
  INNER JOIN PERSOANE P on St.IdPers = P.IdPers
  WHERE P.NumePren = 'Bahrim R Ioana'
);

-- 3. Ce studenti au picat si la 'Baze de date' si la 'Microeconomie'?
SELECT P.NumePren AS 'Nume Elevi ce au picat Baze de date si Microeconomie'
FROM PERSOANE P
INNER JOIN STUDENTI S on P.IdPers = S.IdPers
WHERE S.Matricol IN (
  SELECT DISTINCT E.Matricol
  FROM EXAMENE E
  INNER JOIN DISCIPLINE D on E.CodDisc = D.CodDisc
  WHERE D.DenDisc = 'Baze de date' AND E.Nota < 5
)
INTERSECT
SELECT P.NumePren AS 'Nume Elevi ce au picat Baze de date si Microeconomie'
FROM PERSOANE P
INNER JOIN STUDENTI S on P.IdPers = S.IdPers
WHERE S.Matricol IN (
  SELECT DISTINCT E.Matricol
  FROM EXAMENE E
  INNER JOIN DISCIPLINE D on E.CodDisc = D.CodDisc
  WHERE D.DenDisc = 'Microeconomie' AND E.Nota < 5
);
-- end QUESTIONS region

 

 

Edited by u0m3
Mila
  • Upvote 1
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
Go to topic listing
×
×
  • Create New...