DB - Day5

SQL Server Schema

Default Schema is dbo => Database Owner

Creating schema

CREATE SCHEMA HR
CREATE SCHEMA Sales

Adding tables to schema

ALTER SCHEMA HR TRANSFER Instructor

ALTER SCHEMA Sales TRANSFER Department
SELECT * FROM HR.Instructor

Security

Steps to create a new user and give permissions

  1. Change authentication mode

  2. restart server

  3. create login

  4. create user

  5. create schema

  6. assign objects to schema

  7. Join schema with user

  8. GRANT And DENY

  9. Disconnect then connect with user

  10. New Query

Top

The first two rows

SELECT TOP(2) * 
FROM STUDENT

Maximum Two Salaries

SELECT TOP(2) salary
From Employee
ORDER BY Salary DESC

NewId

This generate GUID

SELECT NEWID()

Give different questions every time (Random)

SELECT TOP(10)
FROM Questions
Order by NEWID()

Full bath Object

[ServerName].[DataBase].[Schema].[Table]

SELECT * FROM [.].[ITI].[dbo].[Student]

Union From different databases Union departments from ITI Database and Company DataBase

SELECT dept_name From Department
Union
Select Dept From Company_SD.dbo.department

Select Into

Create a new table NewTable and copy data from student to it

SELECT * into NewTable
From Students

Only creating structure of employee into new table

SELECT * INTO Table1
FROM Employee
Where 1 =2

insert based on select

the result of select statement will be the new values in Table5

INSERT INTO table5
SELECT * FROM Student Where St_address ='cairo'

Bulk Insert

Insert Data from file (Delimited File)

BULK INSERT table5
FROM 'path to file'
WITH (Filedterminator=',')

Ranking Functions

we Will use this table for the next examples

eid
ename
esal
did

15

ahmed

10000

10

14

ali

10000

10

12

eman

9000

10

1

nada

9000

10

3

khalid

8000

10

7

mohamde

7000

20

8

sayed

7000

20

6

hassan

6000

20

5

omar

6000

20

9

sally

5000

30

10

shimaa

4000

30

11

hana

4000

30

12

lama

3000

30

___

## Row Number

SELECT * ROW_NUMBER() OVER(ORDER BY esal DESC) as RN
FROM Employee
eid
ename
esal
did
RN

15

ahmed

10000

10

1

14

ali

10000

10

2

12

eman

9000

10

3

1

nada

9000

10

4

2

reem

9000

10

5

3

khalid

8000

10

6

7

mohamde

7000

20

7

8

sayed

7000

20

8

6

hassan

6000

20

9

5

omar

6000

20

10

9

sally

5000

30

11

10

shimaa

4000

30

12

11

hana

4000

30

13

12

lama

3000

30

14

___

This Query Will Select the third heights salary

```sql

SELECT *

FROM 
(SELECT * ROW_NUMBER() OVER(ORDER BY esal DESC) as RN
FROM Employee) as NewTable

WHERE RN=3

___
## Dense Rank
```sql
SELECT * DENSE_RANK() OVER(ORDER BY esal DESC) as DR
FROM Employee
eid
ename
esal
did
RN
DR

15

ahmed

10000

10

1

1

14

ali

10000

10

2

1

12

eman

9000

10

3

2

1

nada

9000

10

4

2

2

reem

9000

10

5

2

3

khalid

8000

10

6

3

7

mohamde

7000

20

7

4

8

sayed

7000

20

8

4

6

hassan

6000

20

9

5

5

omar

6000

20

10

5

9

sally

5000

30

11

6

10

shimaa

4000

30

12

7

11

hana

4000

30

13

7

12

lama

3000

30

14

8

___

This Query will select the heights salary with duplication

```sql

SELECT *

FROM 
(SELECT * DENSE_RANK() OVER(ORDER BY esal DESC) as DN
FROM Employee) as NewTable

WHERE DN =1


| eid | ename | esal  | did | RN  | DR  |
| --- | ----- | ----- | --- | --- | --- |
| 15  | ahmed | 10000 | 10  | 1   | 1   |
| 14  | ali   | 10000 | 10  | 2   | 1   |
___
**This Query will get the two heights salaries Employees with duplication**
```sql
SELECT * 
	FROM 
	(SELECT * DENSE_RANK() OVER(ORDER BY esal DESC) as DN
	FROM Employee) as NewTable
WHERE DN <=1
eid
ename
esal
did
RN
DR

15

ahmed

10000

10

1

1

14

ali

10000

10

2

1

12

eman

9000

10

3

2

1

nada

9000

10

4

2

2

reem

9000

10

5

2

___

## NTile

To divide rows into groups

```sql

SELECT * NTile(3) OVER(ORDER BY esal DESC) as G

FROM Employee

```

eid
ename
esal
did
RN
RN
G

15

ahmed

10000

10

1

1

1

14

ali

10000

10

2

2

1

12

eman

9000

10

3

3

1

1

nada

9000

10

4

4

1

2

reem

9000

10

5

5

1

3

khalid

8000

10

6

6

2

7

mohamde

7000

20

7

7

2

8

sayed

7000

20

8

8

2

6

hassan

6000

20

9

9

2

5

omar

6000

20

10

10

2

9

sally

5000

30

11

11

3

10

shimaa

4000

30

12

12

3

11

hana

4000

30

13

13

3

12

lama

3000

30

14

14

3

___

The First Group

```sql

SELECT *

FROM 
(SELECT * NTile(3) OVER(ORDER BY esal DESC) as G
FROM Employee) as NewTable

WHERE G=1


| eid | ename | esal  | did | RN  | RN  | G   |
| --- | ----- | ----- | --- | --- | --- | --- |
| 15  | ahmed | 10000 | 10  | 1   | 1   | 1   |
| 14  | ali   | 10000 | 10  | 2   | 2   | 1   |
| 12  | eman  | 9000  | 10  | 3   | 3   | 1   |
| 1   | nada  | 9000  | 10  | 4   | 4   | 1   |
| 2   | reem  | 9000  | 10  | 5   | 5   | 1   |
___
## Rank

```sql
SELECT * RANK() OVER(ORDER BY esal DESC) as R
FROM Employee
eid
ename
esal
did
RN
RN
G
R

15

ahmed

10000

10

1

1

1

1

14

ali

10000

10

2

2

1

1

12

eman

9000

10

3

3

1

3

1

nada

9000

10

4

4

1

3

2

reem

9000

10

5

5

1

3

3

khalid

8000

10

6

6

2

6

7

mohamde

7000

20

7

7

2

7

8

sayed

7000

20

8

8

2

7

6

hassan

6000

20

9

9

2

9

5

omar

6000

20

10

10

2

9

9

sally

5000

30

11

11

3

11

10

shimaa

4000

30

12

12

3

12

11

hana

4000

30

13

13

3

12

12

lama

3000

30

14

14

3

14

___

## Partition By

Rank Salary For each Partition and divide partitions with Departments and rank each department on its own

```Sql

SELECT * , ROW_NUMBER()

OVER(PARTITION BY di ORDER BY esal DESC) as RN

FROM Employee


| eid | ename   | esal  | did | RN  |
| --- | ------- | ----- | --- | --- |
| 15  | ahmed   | 10000 | 10  | 1   |
| 14  | ali     | 10000 | 10  | 2   |
| 12  | eman    | 9000  | 10  | 3   |
| 1   | nada    | 9000  | 10  | 4   |
| 2   | reem    | 9000  | 10  | 5   |
| 3   | khalid  | 8000  | 10  | 6   |
| 7   | mohamde | 7000  | 20  | 1   |
| 8   | sayed   | 7000  | 20  | 2   |
| 6   | hassan  | 6000  | 20  | 3   |
| 5   | omar    | 6000  | 20  | 4   |
| 9   | sally   | 5000  | 30  | 1   |
| 10  | shimaa  | 4000  | 30  | 2   |
| 11  | hana    | 4000  | 30  | 3   |
| 12  | lama    | 3000  | 30  | 4   |
___
**Top Salary in each department**
```sql
SELECT *
FROM (
	SELECT * , ROW_NUMBER() 
		OVER(PARTITION BY di ORDER BY esal DESC) as RN
	FROM Employee
) as newTable
Where RN=1
eid
ename
esal
did
RN

15

ahmed

10000

10

1

7

mohamde

7000

20

1

9

sally

5000

30

1

___

### Partition by with dense rank

Assigns a dense rank to employees within each department

SELECT * , DENSE_RANK() 
	OVER(PARTITION BY di ORDER BY esal DESC) as DR
FROM Employee
eid
ename
esal
did
RN
DR

15

ahmed

10000

10

1

1

14

ali

10000

10

2

1

12

eman

9000

10

3

2

1

nada

9000

10

4

2

2

reem

9000

10

5

2

3

khalid

8000

10

6

3

7

mohamde

7000

20

1

1

8

sayed

7000

20

2

1

6

hassan

6000

20

3

2

5

omar

6000

20

4

2

9

sally

5000

30

1

1

10

shimaa

4000

30

2

2

11

hana

4000

30

3

2

12

lama

3000

30

4

3

___

```sql

SELECT *

FROM (

SELECT * , DENSE_RANK() 
OVER(PARTITION BY di ORDER BY esal DESC) as DR
FROM Employee) as newTable

WHERE DR =1


| eid | ename   | esal  | did | RN  | DR  |
| --- | ------- | ----- | --- | --- | --- |
| 15  | ahmed   | 10000 | 10  | 1   | 1   |
| 14  | ali     | 10000 | 10  | 2   | 1   |
| 7   | mohamde | 7000  | 20  | 1   | 1   |
| 8   | sayed   | 7000  | 20  | 2   | 1   |
| 9   | sally   | 5000  | 30  | 1   | 1   |
___
## Practice on Database

```sql
SELECT * ,ROW_NUMBER() OVER(ORDER BY st_age DESC) As RN
FROM Student
SELECT * ,DENSE_RANK() OVER(ORDER BY st_age DESC) As DR
FROM Student
SELECT * ,NTile(3) OVER(ORDER BY st_age DESC) As G
FROM Student
SELECT *
FROM (
	SELECT * ,ROW_NUMBER() OVER(ORDER BY st_age DESC) As RN
	FROM Student ) As NewTable
WHERE RN =1

SELECT * 
FROM (
	SELECT * ,DENSE_RANK() OVER(ORDER BY st_age DESC) As DR
	FROM Student ) AS NewTable
WHERE DR =1
SELECT *
FROM (
	SELECT * ,NTile(3) OVER(ORDER BY st_age DESC) As G
	FROM Student) As NewTable
WHERE G=1
SELECT *
FROM (
	SELECT * ,ROW_NUMBER() 
	OVER(PARTITION BY dept_id ORDER BY st_age DESC) As RN
	FROM Student ) As NewTable
WHERE RN =1

SELECT * 
FROM (
	SELECT * ,DENSE_RANK() 
	OVER(PARTITION BY dept_id ORDER BY st_age DESC) As DR
	FROM Student ) AS NewTable
WHERE DR =1
SELECT *
FROM (
	SELECT * ,NTile(3) 
	OVER(PARTITION BY dept_id ORDER BY st_age DESC) As G
	FROM Student) As NewTable
WHERE G=1

Merge

LastTransaction Target Table

Lid
Lname
Lvalue

1

ahmed

3000

2

khalid

4000

3

ali

5000

4

eman

6000

DailyTransaction Source Table

did
dname
dval

1

ahmed

8000

2

khalid

9000

7

nada

10000

To Update Last Transaction Table at the End of day From Daily Transaction

MERGE INTO Lasttransaction as T
Using DailyTransaction As S
ON T.Lid = S.did
WHEN MATCHED THEN 
	UPDATE 
	SET T.Lvalue = S.dval
WHEN NOT MATCHED THEN
	INSERT 
	VALUES (S.did,S.dname,S.Val);
Lid
Lname
Lvalue

1

ahmed

8000

2

khalid

9000

3

ali

5000

4

eman

6000

7

nada

10000

___

MERGE INTO Lasttransaction as T
Using DailyTransaction As S
ON T.Lid = S.did
WHEN MATCHED AND S.dval>T.Lval THEN 
	UPDATE 
	SET T.Lvalue = S.dval
WHEN NOT MATCHED THEN
	INSERT 
	VALUES (S.did,S.dname,S.Val);
Lid
Lname
Lvalue

1

ahmed

8000

2

khalid

4000

3

ali

5000

4

eman

6000

7

nada

10000

___

PreviousDB - Day4NextDB - Day6

Last updated