Employees.sql

use master 
go
use newDB
go

create table employee(
employeeID INT Primary key identity(1,1),
name varchar(100),
age int
)
create table employee_salary(
employeeID int foreign key references employee(employeeID),
name varchar(100),
salary int
)

insert into employee values ('Yousif', 20)
insert into employee values ('Hisham', 53)
insert into employee values ('Fatma', 40)
insert into employee values ('NourElDin', 17)
insert into employee values ('Malek', 13)
insert into employee values ('Asmaa', 32)
insert into employee values ('Abd El Maksood', 35)
insert into employee values ('Alaa', 45)
insert into employee values ('Mohamed', 30)
insert into employee values ('Mossad', 33)
insert into employee values ('Manal', 50)
insert into employee values ('Marwaa', 43)
insert into employee values ('Marwan', 22)
insert into employee values ('Ahmed Salem', 25)
insert into employee values ('Yousif Abd El Kader', 26)
insert into employee values ('Eslam Hesin', 23)
insert into employee values ('Yousif Samy', 22)
insert into employee values ('Ahmed Ashraf', 28)
insert into employee values ('Ahmed Yehia', 30)
insert into employee values ('Mohamed Haridy', 33)
insert into employee values ('Yousif ElOrdi', 35)
insert into employee values ('Yousif Walaa', 32)
insert into employee values ('Eslam Walid', 42)
insert into employee values ('Anas Osama', 26)

insert into employee_salary values (1, 'Data scientist', 20000)
insert into employee_salary values (2, 'Human doctor', 9000)
insert into employee_salary values (3, 'HR', 17000)
insert into employee_salary values (4, 'veterinary', 5000)
insert into employee_salary values (5, 'Engineer', 6000)
insert into employee_salary values (7, 'Sales manger', 15000)
insert into employee_salary values (6, 'veterinary', 7500)
insert into employee_salary values (8, 'pharmacist', 10000)
insert into employee_salary values (9, 'Engineer', 13000)
insert into employee_salary values (10, 'Lawyer', 8500)
insert into employee_salary values (13, 'Data analyst', 18000)
insert into employee_salary values (12, 'Human doctor', 7500)
insert into employee_salary values (11, 'Sales specialist', 10000)
insert into employee_salary values (14, 'Flutter Dev', 6000)
insert into employee_salary values (15, 'Flutter Dev', 10000)
insert into employee_salary values (16, 'Flutter Dev', 25000)
insert into employee_salary values (17, 'Engineer', 15000)
insert into employee_salary values (18, 'Buisness Manger', 14000)
insert into employee_salary values (19, 'AI Engineer', 30000)
insert into employee_salary values (20, 'Data Scientist', 25000)
insert into employee_salary values (21, 'Flutter Dev', 12000)
insert into employee_salary values (22, 'Backend Dev', 35000)
insert into employee_salary values (23, 'Data Analyst', 23000)
insert into employee_salary values (24, 'Data Analyst', 16000)

drop table employee_salary
drop table employee
drop table info

select * from employee
select * from employee_salary
--excecution order =>(from, join, on, where, group, having, select, order, top)
--Database objects =>(Table, view, function, SP, rule)
--[ServerName].[DBName].[ShcemaName].[objectName]

--Data types
--For numeric => {bit, tinyint, smallint, int, bigint}
--for decimal => {smallmoney, money, real, float, dec}
--For strings => {char(10), varchar(10), nvarchar(10), nvarchar(max)}
--char is fixed length charcter
--varchar is variable length charcter that can update the size
--nchar is unicode that allow arabic or english or french
--nvarchar(max) can write up to 2 GB
--Date&Time => {date, time, time(7), smalldatetime, datetime, datetime2(7), datetimeoffset} differs in accurecy and the range of years
--binary DT => {binary, image}
--others => {XML, unique_identifier, sql_variant}

select * from employee where age = 33 order by age
select * from employee where age between 30 and 40
select * from employee where age <> 20
--select * from employee group by age
select count(name) from employee
select distinct(age) from employee
select avg(age) from employee
select distinct(salary) from employee_salary order by salary desc
select avg(distinct(salary)) from employee_salary
select avg(salary) from employee_salary
select count(distinct(name)) from employee_salary

select * from employee_salary
select * from employee
select * from info

--Aggregate functions
select year(getdate())
select month(getdate())
select day(getdate())
select getdate()
select substring('123',2,3)
select avg(salary), name from employee_salary group by name
select min(salary) as minimum_salary, max(salary) as maximum_salary, name from employee_salary group by name
select count(employeeID) as number_of_employees, name  from employee_salary group by name order by number_of_employees desc
select * from employee_salary where salary in(10000,20000, 35000)
select count(employeeID), salary from employee_salary where salary in(10000,20000, 35000) group by salary
select count(employeeID), salary, name from employee_salary where salary in(10000,20000, 35000) group by salary, name
select *, newid() from employee
select * from employee order by newid()--to make it random
select top(3) * from employee order by newid()--to make it random

--Ranking functions
select *, row_number() over(order by salary desc) as rn from employee_salary
select *, dense_rank() over(order by salary desc) as dr from employee_salary
select *, ntile(4) over(order by salary desc) as nt from employee_salary
select *, row_number() over(partition by employeeID order by salary desc) as rn from employee_salary
select *, dense_rank() over(partition by employeeID order by salary desc) as dr from employee_salary
select *, row_number() over(partition by name order by name desc) as rn from employee_salary
select *, row_number() over(partition by name order by salary desc) as rn from employee_salary
select *, dense_rank() over(partition by name order by salary desc) as dr from employee_salary
select *, dense_rank() over(partition by salary order by salary desc) as dr from employee_salary

-- Joins
select * from employee left outer join employee_salary on employee.employeeID = employee_salary.employeeID
select * from employee left outer join employee_salary on employee.employeeID = employee_salary.employeeID full outer join info on id = employee.employeeID
select * from employee full outer join employee_salary on employee.employeeID = employee_salary.employeeID
select avg(salary) from employee_salary where name = 'Data analyst'
select * from employee_salary left outer join employee on employee.employeeID = employee_salary.employeeID where employee_salary.name = 'Data analyst' order by salary, age asc
select * from employee full outer join employee_salary on employee.employeeID = employee_salary.employeeID order by salary, age asc
select * from employee inner join employee_salary on employee.employeeID = employee_salary.employeeID

select age from employee group by age
select salary, count(salary) as counted_salary from employee_salary where salary >= 10000 group by salary order by count(salary) desc, salary desc

--Unions
select * from employee union select * from employee_salary
select * from employee union all select * from employee_salary
select * from employee union all select * from employee_salary order by employeeID

select *, case when age < 20 then 'student' when age between 20 and 30 then 'Junior' when age between 30 and 40 then 'intermidate' when age > 40 then 'senior' end as Categorized from employee inner join employee_salary on employee.employeeID = employee_salary.employeeID order by salary
select *, (salary * 10 / 100) + salary as The_New_Salary from employee_salary

-- cartisian product
select employee.name, employee_salary.name from employee, employee_salary
select employee.name, employee_salary.name from employee cross join employee_salary
select employee.name, employee_salary.name from employee cross join employee_salary where employee.employeeID = employee_salary.employeeID
--self cartisian product
select x.name, y.name from employee as x, employee as y where x.name != y.name
select x.*, y.* from employee as x, employee as y where x.name != y.name order by x.age

--Use of like
select * from employee_salary where name like 'data%'
select * from employee_salary where name like 'p%t'
select * from employee_salary where name like '%r_'
select * from employee_salary where name like '_ata%'
select * from employee_salary where name like '_a%'
select * from employee_salary where name like '%doctor'
select * from employee_salary where name like '%dev'
select * from employee_salary where name like '[vas]%'
select * from employee_salary where name like '[vas][dasi]%'
select * from employee_salary where name like '[^vas]'
select * from employee_salary where name like '[^vas][ua]%'
select * from employee_salary where name like '[^vas]_[ua]%'
select * from employee_salary where name like '[a-h]%'
select * from employee_salary where name like '[^a-h]%'
select * from employee_salary where name like '%[%]'
select * from employee_salary where name like '%[_]%'
select avg(salary) as Avarge_salary_of_developers  from employee_salary where name like '%dev'

--Subqueries
SELECT AVG(age) AS avg_top_10_age FROM (SELECT TOP 10 age FROM employee ORDER BY age DESC) AS top_10_employees
select * from employee_salary where salary > (select avg(salary) from employee_salary)
select *, (select count(name) from employee_salary) from employee_salary

--CTE
with employee_information as (select employee.employeeID, employee.name, age,salary, employee_salary.name as job_title from employee full outer join employee_salary on employee.employeeID = employee_salary.employeeID) select * from employee_information
create table info(id int foreign key references employee(employeeID), full_name varchar(100), age int, salary int, jobtitle varchar(100))
insert into info select employee.employeeID, employee.name, age,salary, employee_salary.name as job_title from employee full outer join employee_salary on employee.employeeID = employee_salary.employeeID
select * from info
select jobtitle, cast(count(jobtitle) as varchar) + ' Person' as num_of_jobs, cast(avg(salary) as varchar) + ' LE' as the_avg_salary from info group by jobtitle order by the_avg_salary desc