Control SQL

create Database Control_SQL_Dardouri

use  Control_SQL_Dardouri


create table Livers(
ISBN int primary key ,
Titre varchar(255) not null,
ThèmePrincipal varchar(255)
)

create table Praticipation(
Identificateur int identity primary key,
ISBN int not null foreign key  references Livers(ISBN),
NumA varchar(255) not null foreign key  references Auteurs(NumA),
Participation varchar(255) default ('Ecriture') check (Participation in ('Ecriture','Introduction','Révision'))
)
create Table Auteurs(
NumA varchar(255) check (NumA like 'A%' and len(NumA) = 4 and cast(right(NumA,3)as int) between 001 AND 999) primary key,
Nom varchar(255) not null,
Prénom varchar(255) not null,
Pseudonyme varchar(255),
Pays varchar(255),
DateNaissance date
)
insert into Livers values (1,'PHP Pour les débutant','Informatique')
insert into Livers values (2,'AJAX par la pratique','Informatique')
insert into Livers values (3,'Conjugision','Langue')
insert into Livers values (4,'Les fondamental SQL','Informatique')
insert into Livers values (5,'Le chemain droit','Islamique')

insert into Auteurs values ('A001','Hallal','Abdessamad','Hillal','Maroc','1/1/1990')
insert into Auteurs values ('A002','Sahri','Anas','Bakh','Saoudie','2/10/1980')
insert into Auteurs values ('A003','Vigtor','Hugo','vguo','France','6/2/1980')
insert into Auteurs values ('A004','Brahim','Dahmane',NULL,'Libya','7/5/1975')
insert into Auteurs values ('A005','azgir','Abdelhaq','Nkba','American','8/6/1986')
insert into Auteurs values ('A006','Badir','Zaid','Tbosi9','Maroc','9/2/1990')

insert into Praticipation values (1,'A001','Introduction')
insert into Praticipation values (2,'A003',NULL)
insert into Praticipation values (1,'A001','Introduction')
insert into Praticipation values (4,'A003','Révision')
insert into Praticipation values (1,'A002','Révision')
insert into Praticipation values (2,'A002',NULL)
insert into Praticipation values (3,'A003','Introduction')
insert into Praticipation values (1,'A003','Ecriture')
insert into Praticipation values (2,'A001','Ecriture')
insert into Praticipation values (5,'A002','Ecriture')

--3. Le code, le nom complet, l’âge de chaque auteur. 

select NumA,Nom,Prénom,DATEDIFF(YEAR,DateNaissance,GETDATE()) as age from Auteurs

--4. Les auteurs les plus âgés. 

select * from Auteurs where DATEDIFF(YEAR,DateNaissance,GETDATE())  >= all (select DATEDIFF(YEAR,DateNaissance,GETDATE())  from Auteurs)

--5. Les auteurs qui n’ont pas de pseudonyme. Ordonnez par code d’auteur. 

select * from Auteurs where Pseudonyme is null order by NumA

--6. Pour chaque pays le nombre d’auteurs. 

select pays,COUNT(*) as nbrAuteurs from Auteurs group by Pays

--7 Le nombre des livres du thème "Informatique".

select COUNT(*) as NbrLivresInfo from Livers where ThèmePrincipal = 'Informatique'

--8. Pour chaque auteur (Code), le nombre de livres dont il a participé. 

select a.NumA,COUNT(*) from Auteurs a,Livers l,Praticipation p where p.ISBN = l.ISBN and p.NumA = a.NumA group by a.NumA

--9. Pour chaque auteur (Code), le nombre de livres écrits. 

select a.NumA from Auteurs a,Livers l,Praticipation p where p.ISBN = l.ISBN and p.NumA = a.NumA and p.Participation = 'Ecriture' group by a.NumA

--10. Afficher pour la question précédente les noms et prénoms des auteurs. 

select Nom,Prénom from Auteurs where NumA in (select a.NumA from Auteurs a,Livers l,Praticipation p where p.ISBN = l.ISBN and p.NumA = a.NumA and p.Participation = 'Ecriture' group by a.NumA)

--11. Pour chaque livre, le nombre d’auteurs ayant participé à son écriture. 

select l.ISBN,COUNT(*) as nbrLue from Livers l, Praticipation p  where l.ISBN = p.ISBN and p.Participation = 'Ecriture' group by l.ISBN

-- 12. Les livres écrits par un seul auteur. 

select p.ISBN  from Praticipation p  where (select count(distinct NumA) from Praticipation where p.ISBN =  ISBN) = 1  and p.Participation = 'Ecriture'

-- 13. Les livres dont l’écrivain est anonyme. 

select l.* from Livers l where l.ISBN in (select p.ISBN from Praticipation p where p.Participation = 'Ecriture' and p.NumA in (select NumA from Auteurs where Pseudonyme='anonyme') )

-- 14. Afficher les participations de chaque auteur comme suit : 

select a.NumA,a.Nom,a.Prénom,
case when  p.Participation = 'Ecriture' then 'OUI' ELSE 'NON' end As [Ecriture],
case when  p.Participation = 'Introduction' then 'OUI' ELSE 'NON' end As Introduction,
case when  p.Participation = 'Révision' then 'OUI' ELSE 'NON' end As [Révision]
from Praticipation p,Auteurs a
where p.NumA = a.NumA

-- 15. Supprimez les auteurs qui n’ont pas de participations.

delete from Auteurs where NumA in (select NumA from Auteurs where NumA not in (select NumA from Praticipation))