Sql Training - 2

SQL Server Identity

Identity column of a table is a column whose value increases automatically. The value in an identity column is created by the server. A user generally cannot insert a value into an identity column. Identity column can be used to uniquely identify the rows in the table.

------

SQL SELECT Statement with GROUP BY clause

The GROUP BY clause is used with the SELECT statement to show the common data of the column from the table:

---------

SQL Aggregate Functions

SQL aggregation function is used to perform the calculations on multiple rows of a single column of a table. It returns a single value.

It is also used to summarize the data.

----------

The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some databases sort the query results in an ascending order by default.

-------------

Types of SQL Aggregation Function

1. COUNT FUNCTION

COUNT function is used to Count the number of rows in a database table. It can work on both numeric and non-numeric data types.

COUNT function uses the COUNT(*) that returns the count of all the rows in a specified table. COUNT(*) considers duplicate and Null.

2. SUM Function

Sum function is used to calculate the sum of all selected columns. It works on numeric fields only.

3. AVG function

The AVG function is used to calculate the average value of the numeric type. AVG function returns the average of all non-Null values.

4. MAX Function

MAX function is used to find the maximum value of a certain column. This function determines the largest value of all selected values of a column.

5. MIN Function

MIN function is used to find the minimum value of a certain column. This function determines the smallest value of all selected values of a column.

-------------------------------------

SQL WHERE

A WHERE clause in SQL is a data manipulation language statement.

WHERE clauses are not mandatory clauses of SQL DML statements. But it can be used to limit the number of rows affected by a SQL DML statement or returned by a query.

Actually. it filters the records. It returns only those queries which fulfill the specific conditions.

WHERE clause is used in SELECT, UPDATE, DELETE statement etc.

----------------------------------

SQL AND

• The SQL AND condition is used in SQL query to create two or more conditions to be met.
• It is used in SQL SELECT, INSERT, UPDATE and DELETE
• Let's see the syntax for SQL AND:
• SELECT columns FROM tables WHERE condition 1 AND condition 2;
• The SQL AND condition require that both conditions should be met.
• The SQL AND condition also can be used to join multiple tables in a SQL statement.
• To understand this concept practically, let us see some examples.

-------------------------------------

SQL OR

The SQL OR condition is used in SQL query to create a SQL statement where records are returned when any one condition met. It can be used in a SELECT statement, INSERT statement, UPDATE statement or DELETE statement

-----------

SQL SELECT AS

• SQL 'AS' is used to assign a new name temporarily to a table column or even a table.
• It makes an easy presentation of query results and allows the developer to label results more accurately without permanently renaming table columns or even the table itself

----------------

HAVING Clause in SQL

The HAVING clause places the condition in the groups defined by the GROUP BY clause in the SELECT statement.

This SQL clause is implemented after the 'GROUP BY' clause in the 'SELECT' statement.

This clause is used in SQL because we cannot use the WHERE clause with the SQL aggregate functions. Both WHERE and HAVING clauses are used for filtering the records in SQL queries

---------------

SQL ORDER BY Clause

• Whenever we want to sort the records based on the columns stored in the tables of the SQL database, then we consider using the ORDER BY clause in SQL.
• The ORDER BY clause in SQL will help us to sort the records based on the specific column of a table. This means that all the values stored in the column on which we are applying ORDER BY clause will be sorted, and the corresponding column values will be displayed in the sequence in which we have obtained the values in the earlier step.
• Using the ORDER BY clause, we can sort the records in ascending or descending order as per our requirement. The records will be sorted in ascending order whenever the ASC keyword is used with ORDER by clause. DESC keyword will sort the records in descending order.
• If no keyword is specified after the column based on which we have to sort the records, in that case, the sorting will be done by default in the ascending order.

Select statements

CREATE TABLE Student_Records   

(  

Student_Id Int PRIMARY KEY,    

First_Name VARCHAR (20),    

Address VARCHAR (20),    

Age Int NOT NULL,  

Percentage Int NOT NULL,  

Grade VARCHAR (10)   

) ;  

INSERT INTO Student_Records VALUES (201, 'Akash', 'Delhi', 18, 89, 'A2'),   

(202, 'Bhavesh', 'Kanpu', 19, 93, 'A1'),  

(203, 'Yash', 'Delhi', 20, 89, 'A2'),    

(204, 'Bhavna', 'Delhi', 19, 78, 'B1'),  

(05,  'Yatin', 'Lucknow', 20, 75, 'B1'),  

(206, 'Ishika', 'Ghaziabad', 19, 51, 'C1'),  

(207, 'Vivek', 'Goa', 20, 62, 'B2');  

SELECT * FROM Student_Records;  

SELECT Student_Id, Age, Percentage, Grade FROM Student_Records;  

CREATE TABLE Employee_Details  

(  

Employee_ID INT identity(1,1) PRIMARY KEY,  

Emp_Name VARCHAR (50),  

Emp_City VARCHAR (20),  

Emp_Salary INT NOT NULL,   

Emp_Panelty INT NOT NULL  

);  

INSERT INTO Employee_Details ( Emp_Name, Emp_City, Emp_Salary, Emp_Panelty)

VALUES ( 'Anuj', 'Ghaziabad', 25000, 500),  

( 'Tushar', 'Lucknow', 29000, 1000),   

( 'Vivek', 'Kolkata', 35000, 500),  

( 'Shivam', 'Goa', 22000, 500);  

select * from Employee_Details

SELECT * FROM Employee_Details WHERE Emp_Panelty = 500; 

CREATE TABLE Cars_Details  

(  

Car_Number INT PRIMARY KEY,  

Car_Name VARCHAR (50),  

Car_Price INT NOT NULL,  

Car_Amount INT NOT NULL  

) ;  

INSERT INTO Cars_Details (Car_Number, Car_Name, Car_Amount, Car_Price)   

VALUES (2578, 'Creta', 3, 1500000),  

(9258, 'Audi', 2, 3000000),   

(8233, 'Venue', 6, 900000),  

(6214, 'Nexon', 7, 1000000);  

SELECT * FROM Cars_Details;  

SELECT COUNT (Car_Name), Car_Price FROM Cars_Details GROUP BY Car_Price;  

CREATE TABLE Employee_Having  

(  

Employee_Id INT PRIMARY KEY,  

Employee_Name VARCHAR (50),  

Employee_Salary INT NOT NULL,  

Employee_City VARCHAR (50)  

);  

INSERT INTO Employee_Having (Employee_Id, Employee_Name, Employee_Salary, Employee_City)   

VALUES (201, 'Jone', 20000, 'Goa'),  

(202, 'Basant', 40000, 'Delhi'),   

(203, 'Rashe', 80000,'Jaipur'),  

(204, 'Aunj', 20000, 'Goa'),  

(205, 'Sumit', 50000, 'Delhi'); 

SELECT SUM (Employee_Salary), Employee_City FROM Employee_Having GROUP BY Employee_City HAVING SUM(Employee_Salary)>5000;  

SELECT AVG (Employee_Salary), Employee_City FROM Employee_Having GROUP BY Employee_City HAVING SUM(Employee_Salary)>5000;  

SELECT MIN (Employee_Salary), Employee_City FROM Employee_Having GROUP BY Employee_City HAVING SUM(Employee_Salary)>5000;  

SELECT MAX (Employee_Salary), Employee_City FROM Employee_Having GROUP BY Employee_City HAVING SUM(Employee_Salary)>5000;

SELECT COUNT (Car_Name), Car_Price FROM Cars_Details GROUP BY Car_Price;  

UPDATE Student_Records  

SET First_Name = 'Tmp'  

WHERE Student_Id = '5'  

UPDATE Student_Records  

SET First_Name = 'beserious', Address = 'test'  

WHERE Student_Id = '5'  

select * from Student_Records;

DELETE FROM Student_Records WHERE Student_Id=201;  

SELECT * FROM Student_Records WHERE Age=19  AND Percentage>30; 

SELECT * FROM Student_Records WHERE Age=19  OR  Percentage>70; 

SELECT Student_Id as 'id', Age, Percentage as 'score', Grade FROM Student_Records;