SQL - Tema de laborator -1

Semestrul MCA-II
CSM-2004
(Sesiunea: 2016-17)
Departamentul de Informatică
AMU
INTRODUCERE

Această sarcină pe DBMS este concepută pentru studenț ii de la MCA-II Semestru. DBMS este un sistem
software care permite unui utilizator să creeze ș i să men ț ină o bază de date. Acesta facilitează procesul de
definirea, construirea, manipularea ș i partajarea bazelor de date între diferi ț i utilizatori. Există
mai multe DBMS pe piaț ă, dintre care Oracle ș i MySQL sunt cele mai populare ș i utilizate pe scară largă.
Oracle oferă o versiune gratuită, ediț ia Oracle Express, pentru studenț i ș i dezvoltatori independenț i.
pentru a efectua sarcini legate de baze de date. MySQL este un SGBD open source întreț inut de Oracle.
Studentul poate folosi orice DBMS, Oracle sau MySQL pentru această sarcină.

OBIECTIV
După finalizarea acestei lucrări de laborator, studenț ii ar trebui să fie capabili să:

Scrie interogări SQL

Designul logic al bazei de date dintr-un diagramă ER
Creează o bază de date completă pentru orice sistem.

INSTRUCȚIUNI LABORATOR

1. Studen ț ii trebuie să trimită următoarele două livrabile pentru fiecare exerci ț iu în mod corespunzător.
semnat de profesor:
Diagramă oER (Numai pentru întrebările 5,6)
o interogare SQL cu output
2. Studen ț ii trebuie să se asigure că fiecare întrebare este evaluată ș i semnată de către
Profesor.
To ț i studen ț ii sunt obliga ț i să predie această lucrare cel târziu până la 28-02-2017.
4. Întârzierea în predare nu va fi acceptată după termenul limită.
5. Cooperează, colaborează ș i explorează pentru cele mai bune rezultate individuale în învă ț are, dar
copierea este strict interzisă.
6. Studen ț ii vor fi judeca ț i după performan ț a lor în clasă, punctualitate,
I. Crea ț i tabelele descrise mai jos:

CLIENT_MASTER
Numele Tabelei:
Description: Folosit pentru a stoca informaț ii despre clienț i.

Numele coloanei Tip de date Size Implicit Attributes

CLIENTNO Varchar2 6
NAME Varchar2 20
ADDRESS 1 Varchar2 30
ADDRESS 2 Varchar2 30
ORAȘ Varchar2 15
COD POSTAL Număr 8
STATE Varchar2 15
COSTELIE Număr 10,2

Table Name: PRODUCT_MASTER

Description: Folosit pentru a stoca informaț ii despre produse.

Column Name Tip de date Size Implicit Atribute

PRODUCTNO Varchar2 6
DESCRIPTION Varchar2 15
PROFITPERCENT Număr 4,2
UNITATEA DE MĂSURĂ
Varchar2 10
CANTITATE DISPONIBILĂ
Number 8
REORDERLVL Număr 8
SELLPRICE Număr 8,2
COSTPRICE Număr 8,2

Table Name: SALESMAN_MASTER

Description: Folosit pentru a stoca informaț ii despre vânzătorii care lucrează pentru companie.

Column Name Data Type Dimensiune

Implicit Atribute
VÂNZĂTOR Varchar2 6
SALESMANNAME Varchar2 20
ADRESA 1 Varchar2 30
ADRESĂ 2 Varchar2 30
ORAȘ Varchar2 20
COD POSTAL Număr 8
STAT Varchar2 20
SALAMT Număr 8,2
TGTTOGET Număr 6,2
YTDSALES Număr 6,2
REMARKS Varchar2 60
 II. Introduce ț i următoarele date în tabelele respective:

(a) Date pentru tabelul CLIENT_MASTER:

ClientNo Name Oraș Cod poș tal State BalDuce
C00001 Ivan Bayross Mumbai 400054 Maharashtra 15000
C00002 Mamta Muzumder Madras 780001 Tamil Nadu 0
C00003 Chhaya Bankar Mumbai 400057 Maharashtra 5000
C00004 Ashwini Joshi Bangalore 560001 Karnataka 0
C00005 Hansel Colaco Mumbai 400060 Maharashtra 2000
C00006 Deepak Sharma Mangalore 560050 Karnataka 0
(b) Date pentru tabelul PRODUCT_MASTER:
ProductNo Description Unitate de profit QtyOn ReorderLvl SellPrice CostPrice
Măsură de procent pentru mână
P00001 Tricouri 5 Piese 200 50 350 250
P0345 Cămăș i 6 bucă ț i 150 50 500 350
P06734 Jeans din bumbac 5 Piese 100 20 600 450
P07865 Blugi 5 Piese 100 20 750 500
P07868 Pantaloni 2 Piese 150 50 850 550
P07885 Puloveri 2.5 Bucă ț i 80 30 700 450
P07965 Cămaș e din denim 4 Piese 100 40 350 250
P07975 Tricouri din Lycra 5 Piese 70 30 300 175
P08865 Fuste 5 Piese 75 30 450 300

(c)Date pentru tabelul SALESMAN_MASTER:

SalesmanNo Name Adresa1 Address2 Oraș PinCode Stat
S00001 Aman A/14 Worli Mumbai 400002 Maharashtra
S00002 Omkar 65 Nariman Mumbai 400001 Maharashtra
S00003 Raj P-7 Bandra Mumbai 400032 Maharashtra
S00004 Ashish A/5 Juhu Mumbai 400044 Maharashtra

SalesmanNo SalAmt TgtToGet YtdSales Remarks

S00001 3000 100 50 Bun
S00002 3000 200 100 Bun
S00003 3000 200 100 Bun
S00004 3500 200 150 Bun

III. Exerciț iu privind recuperarea înregistrărilor dintr-un tabel

a. Afla ț i numele tuturor clien ț ilor.
b. Recupera ț i întreaga con ț inut a tabelului Client_Master.
c. Recupera ț i lista cu numele, ora ș ul ș i statul tuturor clien ț ilor.
d. Lista ț i diversele produse disponibile din tabelul Product_Master.
e. Lista ț i to ț i clien ț ii care se află în Mumbai.
găsi ț i numele vânzătorilor care au un salariu egal cu 3000 Rs.
IV. Exerciț iu pe actualizarea înregistrărilor într-un tabel

a. Schimba ț i ora ș ul ClientNo‘C00005’în‘Bangalore’.

b. Schimba ț i BalDue al ClientNo „C00001” la Rs. 1000.
c. Schimba ț i pre ț ul de cost al 'Pantalonilor' la 950,00 RON.
d. Schimba ora ș ul vânzătorului în Pune.
 V. Exerciț iu privind ș tergerea înregistrărilor dintr-un tabel
a. Ș terge to ț i vânzătorii din Salesman_Master al căror salarii sunt egale cu 3500 Rs.
b. Ș terge toate produsele din Product_Master unde cantitatea disponibilă este egală cu 100.
c. Ș terge din Client_Master unde coloana state con ț ine valoarea 'Tamil Nadu'.

VI. Exerciț iu privind modificarea structurii tabelului

Adăuga ț i o coloană numită 'Telefon' de tip de date 'număr' ș i dimensiune='10' la
tabel Client_Master.
b. Schimba ț i dimensiunea coloanei SellPrice în Product_Master la 10,2.

VII. Exerciț iu pe ș tergerea structurii tabelului împreună cu datele

a. Distruge tabela Client_Master împreună cu datele sale.

VIII. Exerciț iu pe redenumirea tabelului

a. Schimbă numele tabelului Salesman_Master în sman_mast.
 2. Creează tabelele descrise mai jos:
Table Name:CLIENT_MASTER
Description:Used to store client information.
Column Name Tip de date Mărime Implicit Atribute
CLIENTNO Varchar2 6 Cheie primară / prima literă trebuie să înceapă cu 'C'
NUME Varchar2 20 Nu nul
ADRESĂ1 Varchar2 30
ADDRESS2 Varchar2 30
ORAȘ Varchar2 15
COD POȘ TAL Număr 8
STAT Varchar2 15
Baldue Număr 10,2

Table Name:PRODUCT_MASTER
Description:Used to store product information.
Column Name Tip de date Size Implicite Atribute
PRODUCTNO Varchar2 6 Cheia principală / prima literă trebuie să înceapă cu 'P'
DESCRIPTION Varchar2 15 Nu Null
PROFITPERCENT Număr 4.2 Nu Null
UNITATEDEMASURĂVarchar2 10 Nu nul
QTYONHAND Număr 8 Nu nul
REORDERLVL Număr 8 Nu NULL
SELLPRICE Număr 8.2 Nu este nul, nu poate fi 0
COSTPRICE Număr 8.2 Nu este nul, nu poate fi 0

Table Name:SALESMAN_MASTER
Description:Used to store salesman information working for the company.
Column Name Data Type Size Implicit Atribute
VÂNZĂTOR Varchar2 6 Cheia primară / prima literă trebuie să înceapă cu 'S'
SALESMANNAME Varchar2 20 Nu nul
ADDRESS1 Varchar2 30 Nu Null
ADDRESS2 Varchar2 30
CITY Varchar2 20
COD POSTAL Număr 8
STATE Varchar2 20
SALAMT Număr 8,2 Nu NULL, nu poate fi 0
TOTTOGET Număr 6,2 Nu poate fi Null, nu poate fi 0
YTDSALES Număr 6,2 Nu nul
REMARKS Varchar2 60

Table Name:SALES_ORDER
Description:Used to store client’s orders.
Column Name Tip de date Dimensiune Atribute Implicite
ORDERNO Varchar2 6 Cheia primară / prima literă trebuie să înceapă cu 'O'
CLIENTNO Varchar2 6 Cheia externă face referire la ClientNo din tabela Client_Master
ORDERDATE Date Nu nul
DELYADDR Varchar2 25
SALESMANNO Varchar2 6 Cheia externă face referire la SalesmanNo din tabelul Salesman_Master
DELYTYPE Caracter 1 F Delivery: part (P) / full (F)
BILLYN Personaj 1
DELYDATE Date Nu poate fi mai mic decât Data_Comenzii
ORDERSTATUS Varchar2 10 Values (‘In Process’,‘Fulfilled’,‘BackOrder’,‘Cancelled’)
Table Name:SALES_ORDER_DETAILS
Description:Used to store client’s orders with details of each product ordered.
Column Name Date Type Size Implicit Atribute
ORDERNO Varchar2 6 Referinț a cheii străine OrderNo a tabelului Sales_Order
PRODUCTNO Varchar2 6 Referinț a cheii externe ProductNo din Product_Master
masă
QTYORDERED Număr 8
QTYDISP Număr 8
PRODUCTRATE Număr 10,2

2. Introduce ț i următoarele date în tabelele respective:

a. Reinsera ț i datele generate pentru tabelele CLIENT_MASTER, PRODUCT_MASTER ș i

VÂNZĂTOR_MAESTRU.

[Link] pentru tabelul Sales_Order:

OrderNo ClientNo OrderDate SalesmanNo DelyType BillYN DelyDate OrderStatus
O19001 C00001 12-June-04 S00001 F N 20-July-02 În proces
O19002 C00002 25-Iunie-04 S00002 P N 27-Iunie-02 Anulat
O46865 C00003 18-Feb-04 S00003 F Y 20-Feb-02 Împlinit
019003 C00001 03-Apr-04 S00001 F Y 07-Apr-02 Împlinit
O46866 C00004 20-mai-04 S00002 P N 22-Mai-02 Anulat
O19008 C00005 24-May-04 S00004 F N 26-Iulie-02 În Proces

[Link] pentru tabelul Sales_Order_Details:

OrderNo ProductNo QtyOrdered QtyDisp ProductRate
O19001 P00001 4 4 525
O19001 P07965 2 1 8400
O19001 P07885 2 1 5250
O19002 P00001 10 0 525
O46865 P07868 3 3 3150
O46865 P07885 3 1 5250
O46865 P00001 10 10 525
O46865 P0345 4 4 1050
O19003 P03453 2 2 1050
O19003 P06734 1 1 12000
O46866 P07965 1 0 8400
O46866 P07975 1 0 1050
O19008 P00001 10 5 525
O19008 P07975 5 3 1050
3. Folosind tabelele create anterior, genera ț i declara ț iile SQL pentru opera ț ii.
menț ionat mai jos. Tabelele din utilizator sunt după cum urmează:
a. Client_Master
b. Produs_Master
c. Vânzător_Maestru
d. Sales_Order
e. Detalii_Comandă_Vânzare

i) Efectua ț i următoarele calcule pe datele tabelului:

a. Lista ț i numele tuturor clien ț ilor care au 'a' ca a doua literă în numele lor.
b. Lista ț i clien ț ii care locuiesc într-un ora ș al cărui primă literă este 'M'.
c. Lista ț i to ț i clien ț ii care stau în 'Bangalore' sau 'Mangalore'
d. Lista ț i to ț i clien ț ii ai căror BalDue este mai mare decât valoarea 10000.
e. Lista ț i toate informa ț iile din tabela Sales_Order pentru comenzile plasate în luna iunie.
f. Lista ț i informa ț iile despre comenzi pentru ClientNo 'C00001' ș i 'C00002'.
g. Lista ț i produsele al căror pre ț de vânzare este mai mare de 500 ș i mai mic sau egal cu 750.
h. Lista ț i produsele al căror pre ț de vânzare este mai mare de 500. Calcula ț i un nou pre ț de vânzare ca, original
preț de vânzare*.15. Renumeș te noua coloană din rezultatul interogării de mai sus ca new_price.
i. List the names, city and state of clients who are not in the state of‘Maharashtra’.
j. Count the total number of orders.
k. Calcula ț i pre ț ul mediu al tuturor produselor.
l. Determine the maximum and minimum product prices. Rename the output as max_price and
mi9n_price respectiv.
m. Număra ț i numărul de produse care au un pre ț mai mic sau egal cu 500.
n. Lista ț i toate produsele ale căror QtyOnHand este mai mică decât nivelul înregistrat.

ii) Exerci ț iu despre manipularea datelor:

a. Lista ț i numărul de comandă ș i ziua în care clien ț ii au plasat comanda.
b. Lista ț i luna (în litere) ș i data când comenzile trebuie livrate.
c. Lista ț i OrderDate în formatul 'DD-Lună-AA'. De exemplu, 12-Februarie-02.
d. Listează data, cu 15 zile mai târziu decât data de astăzi.
4. Exerci ț ii utilizând clauzele Having ș i Group By:
a. Tipări ț i descrierea ș i cantitatea totală vândută pentru fiecare produs.
b. Găsi ț i valoarea fiecărui produs vândut.
c. Calculează cantitatea medie vândută pentru fiecare client care are o valoare maximă a comenzii de 15000,00.
d. Afla ț i totalul tuturor comenzilor facturate pentru luna iunie.

i) Exerci ț iu despre îmbinări ș i corectare:

a. Afla ț i produsele care au fost vândute lui 'Ivan Bayross'.
b. Afla ț i produsele ș i cantită ț ile care trebuie livrate în luna curentă.
c. Lista ț i numărul de produs ș i descrierea produselor vândute constant (adică produse cu mi ș care rapidă).
d. Găsi ț i numele clien ț ilor care au cumpărat 'Pantaloni'.
e. Listează produsele ș i comenzile de la clien ț ii care au comandat mai pu ț in de 5 unită ț i de 'Pull'
Supraexemplificări.
f. Găsi ț i produsele ș i cantită ț ile pentru comenzile plasate de 'Ivan Bayross' ș i 'Mamta'
Muzumdar
g. Găsi ț i produsele ș i cantită ț ile pentru comenzile plasate de ClientNo 'C00001' ș i
C00002.

ii) Exerci ț iu pe Sub-întrebări:

a. Găsi ț i ProductNo ș i descrierea produselor care nu se mi ș că, adică produsele care nu sunt vândute.
b. List the customer Name, Address1, Address2, City and PinCode for the client who has
comandă plasată nr. 'O19001'.
c. Lista ț i numele clien ț ilor care au plasat comenzi înainte de luna mai, 02.
d. Lista ț i dacă produsul 'Lycra Top' a fost comandat de vreun client ș i imprima ț i Client_no, Nume
cui a fost vândut.
e. Lista ț i numele clien ț ilor care au plasat comenzi în valoare de 10000 Rs sau mai mult.

5. Consideraț i o bază de date CONFERENCE_REVIEW în care cercetătorii îș i depun cercetările

documente pentru consideraț ie. Recenziile realizate de recenzori sunt înregistrate pentru a fi folosite în selecț ia lucrărilor
proces. Sistemul de baze de date se adresează în principal recenzentilor care înregistrează răspunsuri la
întrebări de evaluare pentru fiecare lucrare pe care o revizuiesc ș i fac recomandări cu privire la
fie să acceptăm, fie să respingem lucrarea. Cerinț ele de date sunt rezumate astfel:
a) Autorii lucrărilor sunt identifica ț i în mod unic prin adresa de e-mail. Prenumele ș i numele de familie sunt
de asemenea înregistrat.
b) Fiecare lucrare este atribuită un identificator unic de către sistem ș i este descrisă printr-o
title, abstract, and the name of the electronic file containing the paper.
c) O lucrare poate avea mai mul ț i autori, dar unul dintre autori este desemnat ca
contactaț i autorul.
d) Recenzorii articolelor sunt identifica ț i în mod unic prin adresa de e-mail. Fiecare recenzor
first name, last name, phone number, affiliation, and topics of interest are also
înregistrat.
e) Fiecare lucrare este atribuită între doi ș i patru recenzori. Un recenzor evaluează fiecare
lucrare atribuită lui sau ei pe o scară de la 1 la 10 în patru categorii: tehnic
merit, lizibilitate, originalitate ș i relevanț ă pentru conferinț ă. În cele din urmă, fiecare
recenzentul oferă o recomandare generală cu privire la fiecare lucrare.
 f) Fiecare recenzie con ț ine două tipuri de comentarii scrise: unul care să fie văzut de
comitetul de revizuire doar ș i celălalt ca feedback pentru autor(i).
Proiectaț i diagrama ER ș i construiț i baza de date pentru cele de mai sus. Oferiț i un raț ionament logic pentru aceasta.
proiectarea bazei de date.

[Link]ț i o bază de date MAIL_ORDER în care angajaț ii preiau comenzi pentru piese din
clienț i. Cerinț ele de date sunt rezumate astfel:
a) Compania de vânzări prin coresponden ț ă are angaja ț i, fiecare identificat printr-un angajat unic
numărul, numele ș i prenumele, ș i codul poș tal.
b) Fiecare client al companiei este identificat printr-un număr unic de client, primul
ș i numele de familie, ș i codul poș tal.
c) Fiecare piesă vândută de companie este identificată printr-un număr de piesă unic, un nume de piesă,
preț ș i cantitate în stoc.
d) Fiecare comandă plasată de un client este preluată de un angajat ș i i se atribuie un unic.
număr de comandă. Fiecare comandă conț ine cantităț i specificate de una sau mai multe piese. Fiecare
comanda are o data de primire, precum ș i o dată de expediere estimată. Data reală de expediere este
de asemenea înregistrat.

Proiectaț i un diagramă de Relaț ie-Entitate pentru baza de date de comandă prin poș tă ș i construiț i designul folosind
a data modeling tool such as ERwin or Rational Rose. Create the database for the above said
sistem.

[Link]ă baza de date TOURNAMENT următoare:

[Link]ători(playerID: integer, nume : varchar(50), pozi ț ie : varchar(10), înăl ț ime :
integer, greutate : integer, echipă: varchar(30)). Fiecare jucător este asignat un unic
playerID. Poziț ia unui jucător poate fi fie Gardian, Centru sau Înaintare.
înălț imea unui jucător este în inci, în timp ce greutatea este în lire. Fiecare jucător joacă
doar pentru o singură echipă. Câmpul echipei este o cheie străină către Echipe.

[Link]ă(nume: varchar(30), ora ș : varchar(20)). Fiecare echipă are un nume unic

asociate cu aceasta. Pot exista mai multe echipe din acelaș i oraș .

[Link](gameID: integer, homeTeam: varchar(30), awayTeam : varchar(30),

homeScore : integer, awayScore : integer) Fiecare joc are un gameID unic.
câmpurile homeTeam ș i awayTeam sunt chei externe către echipe. Două echipe pot
se joacă unul împotriva celuilalt de mai multe ori în fiecare sezon. Există un control de integritate pentru a asigura

homeTeamandawayTeamare different.

[Link](playerID : integer, gameID: integer, points : integer, assists : integer,

rebounds : integer). GameStats înregistrează statisticile de performanț ă ale unui jucător în cadrul unui
joc. Un jucător nu poate juca în fiecare joc, caz în care nu va avea statisticile sale.
înregistrat pentru acel joc. gameID este o cheie externă pentru [Link] este o cheie externă pentru
Echipe. Presupuneț i că există deja o verificare a integrităț ii pentru a asigura că jucătorul implicat
aparț ine fie echipei de acasă, fie echipei oaspete.
{"Answers following questions:":"Răspunsuri la întrebările următoare:"}

a. Introduce date adecvate în fiecare tabel (cel pu ț in 10 rânduri).

b. Găsi ț i numele distincte ale jucătorilor care joacă pe pozi ț ia „Gardă” ș i au numele con ț inând
Jo
c. Lista ț i ora ș ele care au mai mult de 1 echipă care joacă acolo.
d. Găsi ț i cei mai înal ț i jucători de la fiecare echipă.
e. Lista ț i toate playerIDs ale jucătorilor ș i asisten ț ele lor medii în toate jocurile de acasă în care au jucat.
f. Pentru fiecare pozi ț ie diferită, găsi ț i înăl ț imea ș i greutatea medie a jucătorilor care joacă pe acea pozi ț ie.
position for the Magic. Output the position first followed by the averages.
g. Lista ț i ID-ul fiecărui jucător, numele lor, echipa ș i numărul de "triplu-dublu" pe care le-au realizat.
câ ș tigate. (Un „triple double” este un joc în care numărul de pase, recuperări al jucătorului,
ș i punctele sunt toate în cifrele duble).
h. List the playerIDs and names of players who have played in at least two games during the
season. Output the playerID and name followed by the number of games played.
i. Găsi ț i echipa(ele) cu cele mai multe victorii în deplasare comparativ cu alte echipe. Afi ș aț i echipa
numele ș i numărul victoriilor în deplasare.
j. List the playerIDs, names and teams of players who played in all away games for their
echipă. Nu reda jucătorii ale căror echipe nu au jucat niciun meci în deplasare.