Baza de date sql

riscanu1 · March 10, 2019

Salut! Mă poate ajuta cineva va rog foarte mult cu rezolvarea acestor 3 cerințe sql?Multumesc!

spider · March 10, 2019

1 hour ago, riscanu1 said:

Salut! Mă poate ajuta cineva va rog foarte mult cu rezolvarea acestor 3 cerințe sql?Multumesc!

https://ibb.co/jrFhxq0

Alte teme mai ai de rezolvat? baga aici.. L-)

u0m3 · March 12, 2019

@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 March 15, 2019 by u0m3
Mila

March 12, 2019

Astea sunt simple rău man, sunt niste join-uri, dacă nici asta nu știi e de rău...

Sign In

Baza de date sql

Recommended Posts

riscanu1

spider

u0m3

Guest

Join the conversation

Browse

Activity

Pages