c12part2, Sql Server

c12part2

Create table Employee (
Emp_id int primary key,
Emp_Fname varchar(30) not null,
Emp_Lname varchar(30) not null,
Age int check  (age>21),
Mail_id varchar (25) unique,
Ph_no bigint unique,
Address varchar (15));
Insert into employee 
values (1, 'Vardhan', 'Kumar', 22, 'vardy@abc.com', 9626088448, 'Delhi'),
 (2, 'Himani', 'Sharma', 32, 'himani@gmail.com', 9962936073, 'Mumbai'),
 (3, 'Aayishi', 'Shreshth', 24, 'aayushi@abc.com', 9047588223, 'Kolkata'),
 (4, 'Hemanth', 'Sharma', 25, 'hemanth@abc.com', 9876540321, 'Bangalore'),
(5, 'Swatee', 'Kapoor', 26, 'swatee@abc.com', 9123456780, 'Hyderabad'),
(6, 'Hema', 'Malini', 25, 'hema@abc.com', 9636088448, 'Chennai'),
 (7, 'hari', 'haran', 22, 'hari@gmail.com', 9970036073, 'Gurguaon'),
 (8, 'ram', 'kumar', 30, 'ram@abc.com', 9045688223, 'Kolkata'),
 (9, 'sanya', 'gupta', 22, 'sanya@abc.com', 9876560321, 'Bangalore')
Select * from employee;
Create table project(
Project_id int primary key,
Emp_id int foreign key references employee(emp_id),
client_id int not null,
Project_name varchar(30) not null,
Starting_date date not null);
Insert into project 
values (111, 1, 3, 'Project1', '2019-04-21'),
 (222, 2, 1, 'Project2', '2019-02-12'),
 (333, 3, 5, 'Project3', '2019-01-10'),
(444, 3, 2, 'Project4', '2019-04-16'),
 (555, 5, 4, 'Project5', '2019-05-23'),
 (666, 9, 1, 'Project6', '2019-01-12'),
(777, 7, 2, 'Project7', '2019-07-25'),
 (888, 8, 3, 'Project8', '2019-08-20')
Select * from project;
Select employee.emp_id, concat(emp_fname, ' ', emp_lname) as Name, project_id, Project_name from employee
inner join project on employee.emp_id=project.emp_id;
Select employee.emp_fname as 'Name', count(project.emp_id) as 'No_Projects'
From employee left join project on employee.emp_id=project.emp_id
Group by employee.emp_fname
Order by count (project.emp_id) desc


run \| edit \| history \| help	0