Scribd
Upload
25 views2 pages

SQL Queries for Admin and Lab Management

The document contains SQL commands for creating a table named 'admin' and performing various queries on related tables such as 'labo', 'composant', and 'ordinateur'. It includes examples of inner joins, selections based on conditions, and aggregation functions like count, min, max, and avg. Additionally, it specifies criteria for filtering results, such as selecting admins supervising labs with computers having more than 8GB of RAM.

Uploaded by

walkdav90
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
25 views2 pages

SQL Queries for Admin and Lab Management

The document contains SQL commands for creating a table named 'admin' and performing various queries on related tables such as 'labo', 'composant', and 'ordinateur'. It includes examples of inner joins, selections based on conditions, and aggregation functions like count, min, max, and avg. Additionally, it specifies criteria for filtering results, such as selecting admins supervising labs with computers having more than 8GB of RAM.

Uploaded by

walkdav90
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

CREATE TABLE admin(matad char(5)primary key , nomad varchar(30),

genread char(1) check(genre='F' or genre='M'),telad char 10));

SELECT [Link], [Link]


FROM admin AS A, labo AS L
jointure
WHERE [Link]=[Link]
AND [Link]='DESIGN';

SELECT nomcomp AS nom_composant FROM composant;

SELECT numlab AS numerolabo


FROM labo
WHERE nomlab='reseau';

SELECT [Link], [Link]


FROM admin AS A INNER JOIN labo AS L
ON [Link]=[Link]
WHERE [Link]='design';

SELECT [Link], [Link]


FROM admin as A,labo AS L
where [Link]=[Link] and [Link]=' mettre le nom ';

affichage composant
SELECT [Link]
FROM labo AS A, ordinateur AS B, installation AS C, composant AS D
WHERE [Link]=[Link]
AND [Link]=[Link]
AND [Link]=[Link]
AND [Link]='reseau';

fonctions d'aggregation

(fonction d'ensemble )

-count : le nomnbre d'enregistrement


-min : la valeur minimale
- max : la valeur maximale
- avg ; la valeur moyenne

noms et genres des admins qui supervise les labo contenant les ordi de plus de 8Gb

SELECT [Link], [Link] , [Link]


FROM admin as A, labo AS L, ordinateur as O
where [Link]=[Link]
AND [Link]=[Link]
AND ram > 8
SELECT COUNT(*) as NbrePC, MIN(ram) as NbrePCfaibleRam, MAX(ram) as NbrePCforteRam
FROM ordinateur;

You might also like