DB - Day4

Aggregate Functions

  • count

  • max

  • min

  • avg

  • sum

Examples will be provided on this employee Table

Table

The Aggregate Functions don't consider NULL values

Sum Of Salaries in employee table

SELECT SUM(salary) 
FROM employes

Result =>87000 Count of employees in your table

SELECT COUNT(eid)
FROM employee

Result =>15 Display maximum and minimum values in salary column

SELECT MIN(salary),MAX(salary)
FROM employee

Result=> 1000,9000 Average of salaries where salary don't equal null

SELECT AVG(salary)
FROM employee

Result=>5800 Average of salaries where salary but replace null with 0

SELECT AVG(ISNULL(salary,0))
FROM employee

Result=>5800

Group By

when using aggregate function and displaying a column we use group by

This means display Every Minimum salary for each Department

SELECT MIN(salary),did
FROM employee
GROUP BY did
salary
did

1000

10

2000

20

1500

30

Display count of employee in every address

SELECT COUNT(eid),address
FROM employee
GROUP BY address
Count(eid)
address

6

cairo

5

alex

4

mansoura

___

Display minimum salary in each departments only including addresses with second letter a

```sql

SELECT MIN(salary),did

FROM employee

WHERE address LIKE '_a%'

GROUP BY did

```

Min(salary)
did

2000

10

2000

20

1500

30

___

**Display Count of employees in each address only including departments 10 and 30

```sql

SELECT COUNT(eid),address

FROM employee

WHERE did IN(10,30)

GROUP BY address

```

Count(eid)
address

3

cairo

3

alex

4

mansoura

___

# Having

### Filtering based on Groups

Having is condition based on aggregate function and is used to filter groups

display sum of every department in the department where sum of salary bigger than > 25000

```sql

SELECT SUM(salary),did

FROM employee

GROUP BY did

HAVING SUM(salary) >25000

```

sum(salary)
did

28000

20

___

Display number of employees in each address with number of employees more than 4

```sql

SELECT COUNT(eid),address

FROM employee

GROUP BY address

HAVING COUNT(eid)> 4

```

count(eid)
address

6

cairo

5

alex

___

The query sums salaries by department for departments with more than 5 employees.

```sql

SELECT SUM(salary),did

FROM employee

GROUP BY did

HAVING COUNT(eid)>5

```

sum(salary)
did

20000

10

___

The query sums salaries by department where addresses have 'a' as the second character and the total salary exceeds 12,000.

```sql

SELECT SUM(salary),did

FROM employee

WHERE address LIKE '_a%'

GROUP BY did

HAVING SUM(salary)>12000

```

sum(salary)
did

15000

20

14500

30

___

```sql

SELECT SUM(salary),address

FROM employee

WHERE address IN(20,30)

GROUP BY address

HAVING COUNT(eid)<4

```

sum(salary)
address

13000

alex

15000

cairo

___

## Practice on Database

SELECT SUM(salary) as total
FROM Instructor
SELECT MAX(salary) as total
FROM Instructor
SELECT MIN(salary) as total
FROM Instructor
SELECT COUNT(*) ,COUNT(st_id),COUNT(st_fname),COUNT(st_age)
FROM Student
SELECT AVG(st_age)
FROM Student
SELECT SUM(salary),dept_id,dept_name
FROM Instructor i INNER JOIN Department d
	on d.dept_id = i.Dept_id
GROUP BY Dept_Id,dept_name

works on combinations of address with departments

SELECT COUNT(st_id),st_addres,Dept_Id
FROM student
GROUP BY st_address,Dept_Id
SELECT SUM(salary) ,dept_id
FROM Instructor
GROUP BY dept_id
HAVING SUM(salary)>25000

Special Case

Having here is considering the table a one group

SELECT SUM(salary),AVG(salary)
FROM Instructor
Having COUNT(ins_id)>100

Set Operators

  • union

  • union all

  • intersect

  • except

--batch
--Set of independent queries
SELECT st_fname
FROM Student

SELECT ins_name
FROM Instructor

Union All

These two tables have no relations The Number of The columns in the two queries must match and data types must match

SELECT st_fname as[names]
FROm Student
UNION ALL
SELECT ins_name
FROM Instructor

Union

Union is distinct the result is without redundant

SELECT st_fname as[names]
FROm Student
UNION 
SELECT ins_name
FROM Instructor

Intersect

The matched data in the two tables

SELECT st_fname as[names]
FROm Student
INTERSECT 
SELECT ins_name
FROM Instructor

Except

The data in the first table but don't exist in the second table

SELECT st_fname as[names]
FROm Student
EXCEPT 
SELECT ins_name
FROM Instructor

Subquery

taking output of queries as input in another queries The Inner query runs First Then outer query runs based on that outpu

SELECT *
FROM Student
WHERE st_avg < (SELECT AVG(st_age) FROM Student)
SELECT * ,(SELECT COUNT(st_id) FROM Student)
FROM Student

Display departments with students in it

SELECT dept_name
FROM Department
WHERE Dept_Id in (SELECT DISTINCT dept_id
				FROM Student
				WHERE Dept_Id IS NOT NULL)

Subquery With DML

DELETE FROM Student_course
WHERE Crs_id =100
DELETE FROM Student_course
WHERE Crs_id =(SELECT crs_id FROM Course 
				WHERE Crs_name ='OOP')
SELECT st_fname,dept_id,st_age
FROM Student 
WHERE St_address ='cairo'

Order by first column

SELECT st_fname,dept_id,st_age
FROM Student 
ORDER BY 1

Order by department id ascending if there two matched order by age Descending

SELECT st_fname,dept_id,st_age
FROM Student 
ORDER BY Dept_id ASC, st_age DESC
SELECT st_fname +' '+St_lname AS Fullname
FROM Student
ORDER BY Fullname
SELECT *
FROM (SELECT st_fname + ' '+st_lname as fullname
		FROM Student) as newtable
WHERE fullname='ahmed mohamed'

Execution Order

  1. from

  2. join

  3. on

  4. where

  5. group by

  6. having

  7. select

  8. order by

  9. top

Batch

is set of independent queries

DDL Queries cant run in the same batch you must use go Keyword the right queries run and the wrong don't but they don't affect on each other

Script

is set of independent queries Separated by go Keyword

Transactions

Set of Independent queries run as a single unit of work all queries run together or no query run at all

Every Query is Implicit Transaction

Explicit Transaction

BEGIN TRANSACTION
INSERT 
UPDATE
DELETE 
COMMIT OR ROLLBACK

Examples

BEGIN TRY
	BEGIN TRANSACTION
	
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK
	SELECT ERROR_LINE(),ERROR_MESSAGE(),ERROR_NUMBER()
END CATCH

Every Transaction have four Properties ACID

  • Atomicity - each statement in a transaction (to read, write, update or delete data) is treated as a single unit. Either the entire statement is executed, or none of it is executed. This property prevents data loss and corruption from occurring if, for example, if your streaming data source fails mid-stream.

  • Consistency - ensures that transactions only make changes to tables in predefined, predictable ways. Transactional consistency ensures that corruption or errors in your data do not create unintended consequences for the integrity of your table.

  • Isolation - when multiple users are reading and writing from the same table all at once, isolation of their transactions ensures that the concurrent transactions don't interfere with or affect one another. Each request can occur as though they were occurring one by one, even though they're actually occurring simultaneously.

  • Durability - ensures that changes to your data made by successfully executed transactions will be saved, even in the event of system failure.

Drop ,Delete, truncate

DROP
DELETE
TRUNCATE

DDL

DML

DDL

Delete data and meta data

Delete data

delete data

Can use where clause

can't Use where delete all table data

Slower

Faster

Can Rollback

Can't Rollback

Don't reset identity

reset identity

always Log

sometimes log

___

## Identity

use this query when you want to manually insert values in identity

```sql

SET IDENTITY INSERT Table_1 ON;

```

Can't insert identity manually

```sql

SET IDENTITY INSERT Table_1 OFF;

```

Check the last identity value in the last query

```sql

SELECT SCOPE_IDENTY()

SELECT @@IDENTITY

```

Check Identity in specific table

```sql

SELECT IDENT_CURRENT('t1')

```

The current database you are using

```sql

SELECT db_name()

```

The name of the user that is currenty login in server

```sql

SELECT suser_name()

```

The name of the host server

```sql

SELECT host_name()

```

___

## Some Build in Functions

  1. Upper

  2. lowe

  3. len

  4. substring

  5. concat

  6. concat_ws

Display first name in upper case and last name in lower case

SELECT upper(st_fname) ,lower(st_name)
FROM Student

Display the length of first name

SELECT len(st_fname),st_fname
FROM Student

sub string start from first char and select 3 chars

SElECT substring(st_fname,1,3)
FROM Student

Concatenation some strings and concatenation with separator

SELECT Concat('ahmed','ali','omar')--ahmedaliomar
SELECT Concat_ws(',','ahmed','ali','omar')ahmed,ali,omar
PreviousDB - Day3NextDB - Day5

Last updated