Scribd Logo
Upload

Welcome to Scribd!

  • 20 views

    Atelier Base de Données SQL

    Uploaded by

    Momed
    The document describes SQL commands used to create a database table for employees, insert data, update and delete rows, and perform various queries on the employee table. The table stores information like employee IDs, names, nationality, age, gender, department, and salary. The document runs queries to select, filter, aggregate, group and order the employee data in different ways.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Atelier Base de Données SQL

    Uploaded by

    Momed
    20 views6 pages
    The document describes SQL commands used to create a database table for employees, insert data, update and delete rows, and perform various queries on the employee table. The table stores information like employee IDs, names, nationality, age, gender, department, and salary. The document runs queries to select, filter, aggregate, group and order the employee data in different ways.

    Original Title

    sql cours

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document describes SQL commands used to create a database table for employees, insert data, update and delete rows, and perform various queries on the employee table. The table stores information like employee IDs, names, nationality, age, gender, department, and salary. The document runs queries to select, filter, aggregate, group and order the employee data in different ways.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    20 views6 pages

    Atelier Base de Données SQL

    Uploaded by

    Momed
    The document describes SQL commands used to create a database table for employees, insert data, update and delete rows, and perform various queries on the employee table. The table stores information like employee IDs, names, nationality, age, gender, department, and salary. The document runs queries to select, filter, aggregate, group and order the employee data in different ways.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    You are on page 1of 6

    Atelier Base de données SQL

    1/

    create table employe

    (Id_emp number primary key ,

    prenom varchar(30) not null,

    nom varchar(30) not null,

    nationalite varchar(40),

    age integer check (age >= 20 and age <= 60),

    sexe char(1),

    service varchar(25),

    salaire number (10,3) check (salaire>0) );

    --Table créée

    2/

    insert into employe values ( 10,'Mohamed', 'Amine', 'TUN', 27, 'M', 'Comptabilité', 1000);

    insert into employe values ( 20,'Angela', 'Portenca', 'ITA', 30, 'F', 'Informatique', 1200);

    insert into employe values ( 30,'Taher', 'Tlili', 'ALG', 35, 'M', 'Comptabilité', 870);

    insert into employe values ( 40,'Amira', 'Tounsi', 'TUN', 47, 'F', 'Personnel', 570);

    insert into employe values ( 70,'Sonia', 'Tounsi', 'TUN', 40, 'F', 'Informatique', 960);

    insert into employe values ( 90,'Salah', 'Ali', 'TUN', 25, 'M', 'Comptabilité', 1400);

    insert into employe values ( 80,'Amir', 'Amor', 'TUN', 55, 'M', 'Marketing', 1200);

    insert into employe

    values ( 100,'Amina', 'Ali', 'TUN', 22, 'F', 'Comptabilité', 1500);

    3/

    Exécution
    4/

    update employe

    set salaire = salaire * 1.1 ;

    5/

    update employe

    set salaire = salaire * 1.15

    where nationalite ='TUN' ;

    6/

    UPDATE employe set service = 'finance' where service = 'Comptabilité';

    7/

    delete from employe

    where service = 'Marketing';

    8/

    delete from employe where id_emp = 100 ;

    9/

    select nom,prenom from employe ;

    10/

    *) select Nationalite

    from employe ;

    *) select distinct Nationalite from employe ;

    11/

    select * from employe ;

    12/

    select nom,prenom

    from employe

    where service ='Informatique' ;

    13/

    select nom,prenom,age from employe where age <50 ;


    14/

    select nom ,prenom

    from employe

    where prenom like '%m%' ;

    15/

    select nom , prenom ,age from employe where nom like 'w???r' ;

    16/

    *) select prenom , nom from employe

    where not(service ='Personnel');

    *) select prenom , nom from employe

    where service <>'Personnel';

    17/

    select prenom ,nom from employe where nom not like 'T%';

    18/

    select prenom from employe where service ='Informatique' and age<30 ;

    19/

    select prenom,nom

    from employe where sexe=’F’ and service <>’personnel’ ;

    20/

    SELECT prenom, nom

    FROM Employe

    WHERE (nationalite='TUN' OR nationalite='ALG') AND Sexe='F';

    21/

    SELECT prenom, nom ,nationalite FROM Employe WHERE nationalite='TUN' OR nationalite='ALG'


    AND Sexe='F';

    22/

    insert into employe values

    (24,'adel','bouallem','Alg',,'M',,1000);
    23/

    select * from employe where age is null ;

    select * from employe where service is null ;

    24/

    *) select id_emp , nom , prenom , age from employe

    where age between 30 and 50 ;

    *) select id_emp , nom , prenom , age from employe

    where age > 30 and age <50 ;

    25/

    select *from employe where (sexe='m' and (age between 20 and 30))

    or (sexe='f' and (age between 40 and 50));//and est plus fort que or

    26/

    select id_emp , nom , age , service from employe where service in


    ('comptbilité','informatique','vente');

    27/

    select id_emp , nom , age , service from employe where

    service ='Comptabilité' or service = 'informatique' or service = 'vente';

    28/

    select * from employe where sexe='m' and age <45 and nationalite not in ('tun', 'ita', 'alg');

    29/

    select prenom , nom , salaire*0.20 as "commission" from employe ;// select prenom , nom ,
    salaire*0.20 as commission from employe ;
    30/

    select nom , prenom , salaire from employe where sexe ='m' order by salaire desc ;

    31/

    select * from employe order by (nationalite) , (salaire) desc ;// tri nation apr salaire

    32/

    select avg(age)

    from employe where nationnalité=’TUN ‘ ;

    33/ select sum (salaire)

    from employe where nationalite ='alg' ;

    34/select count(*)

    from employe where nationalite ='tun';

    35/

    select count(distinct(nationalite))

    from employe ;

    36/

    select sum(salaire*0.20) from employe ;

    37/

    select avg(salaire) , service

    from employe

    group by (service)

    order by (service) desc ;


    38/

    select avg(salaire) , service from employe

    where nationalite = 'tun'

    group by (service) order by (service) desc ;

    39

    select count(*) , sum(salaire) , service

    from employe

    group by (service) ;

    40/

    select count(*) , sum(salaire) , nationalite

    from employe

    group by nationalite

    having(count(*)>1);

    41/

    select count(*) , sum(salaire) , nationalite

    from employe

    where not(nationalite = 'alg')

    group by nationalite

    having(count(*)>1);

    You might also like