SQL | DIVYA'S BLOG

Data :
It is a collection of values and facts for, the purpose of statics calculation and manipulation. The Data becomes random in nature.

Database :
A Database is a collection of related data organised in a way that data can be easily accessed, managed and updated. Database ban be software based or hardware based.

DBMS :

DBMS acronym is Database Management System.
A DBMS is a software that allows creation, definition and manipulation of database, allowing users to store, process and analyse data easily.
DBMS provides us with an interface or a tool, to perform various operations like creating database, storing data in it, updating data, creating tables in the database.
DBMS also provides protection and security to the databases. It also maintains data consistency in case of multiple users.

Block diagram of DBMS

RDBMS :

RDBMS acronym is Relational Database Management System.
RDBMS is a software system which is used to store data in the form of tables.
All modern database management systems like SQL, MS SQL Server, IBM DB2, ORACLE, My-SQL and Microsoft Access are based on RDBMS.

Block Diagram of RDBMS

DBMS Vs RDBMS :

DBMS	RDBMS
Data Base Management System	Relational Data Base Management System
DBMS stores data as a file.	In RDBMS data is stored in the form of tables.
DBMS supports single user only.	RDBMS supports multiple users.
DBMS does not support Normalization.	RDBMS can be normalized.
DBMS does not support client-server architecture.	RDBMS supports client-server architecture.
In DBMS no relationship between data	In RDBMS data is stored in the form of tables which are related to each other with the help of foreign keys.
There is no security.	Multiple levels of security.
Data elements need to access individually.	Data can be easily accessed using SQL query. Multiple data elements can be accessed at the same time.
Examples of DBMS are a file system, XML, Windows Registry, etc.	Example of RDBMS is MySQL, Oracle, SQL Server, etc.

Relation between DBMS and RDBMS

Different types of RDBMS :

Ms SQL Server
MySQL
Oracle
Ms Access
Sybase

Authentication :

Authentication is the process of determining whether someone or something to be declared. Authentication provides access control for systems by checking to see if a user's credentials match the credentials in a database of authorised users.

SQL Authentication :

Windows authentication

SQL Server authentication

Windows authentication :
When you are accessing SQL Server from the same computer it is installed on, you shouldn't be prompted to type in an username and password.

Connection of windows authentication

SQL Server authentication :

SQL Server authentication manages the created account and password. This information is stored in the Master Database.

Connection of SQL authentication

How to connect to the SQL server remotely :

There are two types of adjustments which must be set before connecting to the remote SQL Server.

Protocol being requested(TCP/IP).
Windows Firewall.

Remotely connected to the SQL Server

Create a new user to SQL Server :

The following procedure is followed to create a new user to SQL Server:

Open SQL Server Management Studio and connect to SQL Server by using windows authentication or SQL Server authentication.

Connected to the windows authentication

In the object Explorer of SQL Server Management Studio, click on Security folder and expand it.

Security folder

Right-click the Logins folder and choose New Login. The dialog box opens. Select the general page, and then enter a user name in the Login name text box.
Select SQL Server Authentication and enter password. And unchecked the enable password policy.

Select the Server Roles page, and then check the sysadmin check box in the Server roles list.

To select Server roles

Select the user mapping page and click on the default database, db_owner.

To choose User Mapping

Click OK.

Complete creation of user

Now open SQL Server Management Studio and to Login with the new Login and password.

With new user

New user of testTable

Database creation :

Using SSMS.
Using SQL query

Using SSMS :

Connect SQL Server Management Studio with SQL Server authentication. In object explorer right click on database and click New database enter database name and click OK.

Creation of Database

Database name with Divya

Using SQL query :

Connect SQL Server Management Studio with SQL Server authentication. Open new query window type database query and execute.

Database query

Creation of new database using query

Create a new user to only specific database :

The following procedure is followed to create a new user to SQL Server:

Connected to the windows authentication

Security folder

Select SQL Server Authentication and enter password. And unchecked the enable password policy.

To create user to the particular database

Server Roles

Complete user creation of desired database

Where the SQL files are stored :

To see the file path following steps are:

Select files page.

To see master file paths :

To see master database file path

To see different databases in SQL Server :

Complete database list

To see specific database file paths :

Specific database file paths

MDF and LDF :

MDF:

MDF acronym is Master Database file.
The MDF is the primary data file for MSSQL. It contains all the main information about the database.

LDF:

LDF acronym is Log Database file.
This file stores information related to transaction data file. The LDF stores changes related to inserts, deletion, updates, addition etc.
The information that this file stores ranges from date/time of change, details of the changes made, as well as information related to whoever made the changes.

Create a Database for college using SQL query :

Database creation of college

Create a Database for college using UI(User Interface) :

Open SQL Server Management Studio right click on database select new database.

Databse creation with UI

Databse of Clg

Drop Database using SQL query :

Drop database using query

Drop Databse using UI :

Drop database using UI

Creation of Table :

Insert data into table :

Extraction of table data :

Table output :

Drop table of Teacher :

Truncate table of student :

Add new column to the student table :

Drop column in student table :

Change the column Data type :

Change column size :

Change column name :

How to change column constraint :

Select the data to XML type :
We convert the data table into the XML format using 3 types

Ex 1: XML Raw

Output

Ex 2: XML Path

Ex 3: XML Auto

Using constraints :

1. Primary Key
By using primary key we cannot insert same value

2. Not Null
If we insert null value in mobile number field the following error is occurred.

3. Unique
By using this we cannot insert duplicate key in table. The duplicate key is (Abhigna)

Unique key accepts only one null value

Difference between primary key and unique key :

Primary key	Unique key
Primary key can't accept any null values.	Unique key can accept only one null value.
We can have only one primary key in a table.	We can have more than one unique key in a table.
Is used to identify record in a table.	Is used to prevent duplicate values in a column.

4. Default

Create table with default constraint:
The DEFAULT constraint is used to provide a default value for a column.

Insert values into table :

Output of Default key :

5. Check
The CHECK constraint is used to limit the value range that can be placed in a column.

Create table with default constraint :

6. Foreign key
A FOREIGN KEY is a key used to link two tables together. A FOREIGN KEY is a field in one table hthat refers to the PRIMARY KEY in another table.

Drop Foreign key :

Alter Foreign key :

7. Auto Increment
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.

Add all constraints to students and teachers table :

Using insert into tables to perform a insert 10 records, null values and update a table :

Update table with condition :

Delete record from table :

Delete record from table with condition :

By using SELECT display Teachers data :

Select with WHERE condition :

Select IN :

The IN operator allows you to specify multiple values in a WHERE clause.

Select NOT IN :

The NOT IN operator does not allows you to specify multiple values in a WHERE clause.

Select Like :

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

Ex 1: 's%' means name starts with (s)

Ex 2: '%a' means name end with (s)

Ex 3: As like same name

Select Distinct :

The SELECT DISTINCT statement is used to return only distinct(different) values.

Select DISTINCT count :

SELECT BETWEEN :
The BETWEEN operator selects values within a given range.

SELECT TOP keyword :
The SELECT TOP clause is used to specify the number of records to return.

SELECT ORDER BY keyword :
The ORDER BY keyword is used to sort the result-set in ascending of descending order.

Aliases applied to the column :
SQL aliases are used to give a table, or a column in a table, a temporary name.

Aliases applied to the table :

Using AND operator with WHERE clause :

Using OR operator with WHERE clause :

SQL commands :

SQL functions :

1. Numeric functions/Mathematical functions

2. String functions :

Isnull() function :

Joins :

What is join ?

A join is used for combined the fields for two or more tables by using values common to each.

Types of Joins :

Inner join.
Left outer join.
Right outer join.
Full outer join.
Self join.

Inner join :

The INNER JOIN keyword selects records that have matching values in both tables.

Select student name, mobile number and teacher name from two tables using inner join :

Left join :

The LEFT JOIN keyword returns all records from the left table(table 1), and the matched records from the right table(table 2).

The result is NULL from the right side, if there is no match.

Select student name, mobile number and teacher name from two tables using Left join :

Right join :

The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1).
The result is NULL from the left side, when there is no match.

Full Outer join :
The FULL OUTER JOIN keyword return all records when there is a match in either left or right table records.

Self join :

A self JOIN is a regular join, but the table is joined with itself.

Using inner join display orders with customer name :

Using left join to display all the orders with customer name :

Using right join to display all the customers with orders :

Using full join to display all the customers and orders :

Using self join to display customers from same country :

Union :

The SQL UNION clause is used to combine the results of two or more SELECT statements without returning any duplicate rows.

Each SELECT statement within UNION must have the same number of columns.
Same data type and same order.

How To display city, country using UNION operator :

WHERE clause applied in UNION operator :

Union All :

The UNION ALL operator is used to combine the results of two SELECT statements including duplicate rows.

To display city, country using UNION ALL operator :

Comparision of UNION & UNION ALL :

SQL INTERSECT operator :
The SQL INTERSECT clause/operator is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement. This means INTERSECT returns only common rows returned by the two SELECT statements.

SQL EXCEPT operator :
The SQL EXCEPT clause/operator is used to combine two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement. This means EXCEPT returns only rows, which are not available in the second SELECT statement.

Group by single field :

Group by multiple field :

Using GROUP BY to display Name, mobile number & email :

Using WHERE & GROUP BY clause :

Group By :
Create a table for departments in your college like.. ECE, EEE,IT,CSE.. and in teachers table assign the teachers to departments and get the departments wise teachers count

Using simple query with WHERE clause :

Using GROUP BY & HAVING Clause :

SELECT INTO statement :
The SQL Server SELECT INTO statement is used to create a table from an existing table by copying the existing table's columns.

Syntax :
SELECT * INTO newtable FROM oldtable
WHERE condition;

Using SELECT INTO statement to copy all columns in table:

Copy selected columns into a new table :

Using INSERT INTO SELECT statement :

The INSERT INTO SELECT statement copies data from one table and inserts it into another table.

Sample Database ERD :

To get supplier list in supplier table :

To get customers according to the supplier :

To get maximum sold products :

To get minimum sold products :

To get not sold products list :

To get customer order of product list :

To get greater than 30 of unit price :

To get less than 30 of unit price :

To get maximum sold products as per city, country of supplier :

To get minimum sold products as per city, country of supplier :

To get maximum products sold by city, country as per customer :

To get minimum products sold by city, country as per customer :

To get which customer orederd more product :

To get which customer ordered less products :

To get last record of product in product table :

How to select products matching only IN list in a given product table :

How to get sold products as per country, city, in supplier table :

Choose products as per orderDate in order table :

To join customer,order,orderitem,product & supplier :

Top 20 products in the product table :

Package details for products :

In which date maximum products are ordered :

Which supplier maximum products supplied to the customer :

Which supplier minimum products supplied to the customer :

Which supplier not supplied products to the customer :

Calculating orders & quantity :

Total orders for each product :

Calculating the sum of Quantity and Unit price :

To calculate Average amount to the quantity of items :

Products list in Product table :

To get customer with no orders :

To get customer details in year and month :

Get me the Count of customers from each city :

Get me the City where we have customers but no suppliers :

Get me the city where we have supplier but no customer :

Get me the customer who given highest order :

Get me the high sold items :

Get me the item which is not sold as of now :

Get me the order which has maximum items on order :

Get me the customer's wise orders items list :

Get me the customer who taken maximum items :

Get me the sold Items with customer and supplier details :

To get department wise HOD :

To get Department wise Employees and Total salary :

Using Exception handling in SQL :

Use of BEGIN and END in SQL :
This keywords are used to process a multiple code blocks in SQL

Use of [--] in SQL :
The square brackets [ ] are used to delimit identifiers. This is necessary if the column name is a reserved keyword or contains special characters such as a space or hyphen.

TCL-Commands :
Transaction Control Language(TCL) commands are used to manage transactions in the database. These are used to manage the changes made to the data in a table by DML statements.
1. COMMIT
2. ROLLBACK

COMMIT command :
COMMIT command is used to permanently save any transaction into the database. When we use Commit in any query then the change made by that query will be permanent and visible. We can't Rollback after the Commit.
Syntax :
COMMIT;

Ex :

Rollback command :

Rollback is used to undo the changes made by any command but only before a commit is done. We can't Rollback data which has been committed in the database with the help of the commit keyword.

Ex :

PRINT statement in SQL :
In Sql Server PRINT statement can be used to return message to the client. It takes string expression as input and returns string as a message to the application.

IF.. ELSE in SQL Server :

Ex:

ELSE--IF IN SQL Server :

While loop in SQL :

While loop with Break :

Temporary tables :

1. Temporary tables are particularly useful when you have a large number of records in a table and you repeatedly need to interact with a small subset of those records.

2. In such cases instead of filtering the data again and again to fetch the subset, you can filter the data once and store it in a temporary table.

3. You can then execute your queries on that temporary table. Temporary tables are stored inside “tempdb” which is a system database.

Temporary tables are two types :

1. Local Temporary tables

Syntax :

#Temporary_tableName

2. Global Temporary tables

Syntax :

##Temporary_tableName

Local Temporary tables :

Ex :

Where Temporary tables are stored in DataBase :

Global Temporary tables :

They are dropped when the last connection using them is closed.

Ex :

Ex :

User defined functions in SQL :

There are two types,

1. Scalar valued functions

2. Table valued functions

Scalar valued functions :

A Scalar UDF accepts zero or more parameters and return a single value. The return type of a scalar function is any data type.

Create scalar function :

To call a scalar function :

View a scalar function :

Table valued functions :

These are two types,

1. Inline table valued functions

2. Multi line table valued functions

Inline table valued functions :

The Inline function returns a table data type as the return value based on a single SELECT statement.

Multi line table valued functions :

Ex :

Scalar Function: Write a function to get the customer sales as of a particular date Function (CustomerID, OrderDate) Return total sale for that day :

Ex :

Table-Valued Functions: Write a function to print multiplication table Function(number(8) int) Return table like :

[ 8 X 1 = 8

8 X 2 = 16

8 X 10 = 80 ]

Multiplication of given num statically :

Database creation of Bikes :

Identity used in column :

1. When the id column even though it has identity if we insert a values into the table the statement had got terminated because the column did not specified with not null

2. Even though the manual data inserted in the identity column is unique the statement is terminated as the column is not specified with “not null”

3. If we give a column name as identity, not null that case only inserted values are automatically increased

4. When a table column is added with random column data without identity while creating the table then identity is added by using ssms, then the auto increment is executed after last inserted column(divya).

5. When a data is inserted in the second column then the identity column was incremented

6. If we insert a values to specified position to the table

7. Even though we insert a same value in table that value also inserted because the identity is set to ON state

8. If we insert a values into table it cannot insert because identity is ON state so to OFF identity

Difference between varchar and nvarchar :

Varchar ():

1.Varchar uses one bytes per character.

2. Variable-length, non-Unicode string data.

3. Can store Values upto 8000 characters.

Nvarchar():

1. Varchar uses two bytes per character.

2. Variable-length, Unicode string data.

3. Can store Values upto 4000 characters.

Difference between Truncate and Delete :

Delete	Truncate
DELETE is a DML command.	TRUNCATE is a DDL command.
DELETE is executed using a rowlock and.	TRUNCATE is executed using a table lock and whole table is locked for remove all records.
We can use where clause with DELETE to specific records.	We cannot use Where clause with TRUNCATE.
The DELETE command is used to remove rows from a table based on WHERE condition.	TRUNCATE removes all rows from a table.
It maintain the log, so it slower than TRUNCATE.	Minimal logging in transaction log, so it is performance wise faster.
The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.	TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.
Delete uses the more transaction space than Truncate statement.	Truncate uses the less transaction space than Delete statement.
Data is rolled back in delete.	Data is cannot rolled back in truncate
Identity of column keep DELETE retain the identity.	Identify column is reset to its seed value if table contains any identity column.

Truncate operation :

Delete operation :

To see transaction log database size & log space :
DBCC sqlperf(logspace): Tells you the current log file size and the percentage of log space used.

To see table rows in sql server :

DBCC :
Database consistency checker. DBCC commands act as database console commands which means that they are used to check the consistency of SQL server database. They are use for maintenance of database, tables, filegroups, indexes.

To see data consistency of current database :
DBCC checkdb: Checks your current database for page level errors or inconsistencies and provides an option to try and fix them as well (data loss highly probable).

To see the SQL server logs :

1. In Object Explorer, expand the Management section.

2. Right-click SQL Server Logs, select View, and then choose SQL Server Log.

How to add auto increment to the existing table :

Using SSMS to add auto increment. In this process to click on database and right click on primary key column to set identity has yes.

How to rename database name in SQL server :

how to enable mixed mode i.e, SQL server & windows authentication if we select a windows mode authentication In installation process :

1. Open SQL Server Management Studio Express. 2. Choose Windows Authentication from the Authentication drop down list. 3. Right-click the SQL Server and choose Properties to open the Server Properties - window. 4. Click Security on the left hand side. 5. Choose SQL Server and Windows Authentication Mode on the right side. 6. Click OK to close the Server Properties - window.

7. Right-click the SQL instance and choose Restart. 8. Click Yes to restart the SQL server.

Relationship between supplier and products :

Database design of bikes : Supplier table :

Branch Details table :

Product table :

Orders table :

Order Details table :

Shipping Details table :

View :
1. Unlike table Views don't occupy space on physical storage and it don't have schema.
2. The main usage of VIEW is to hide few columns from the table which you may not want to share with the users who wants only the required and relevant data.
3. Views reduce the effort for writing queries to access specific columns every time.

Difference between View and Table :

View	Table
VIEW is also a database object which is used as a table and query that can be linked to be other tables.	Table is an object of database which is used to hold data that are used in reports and applications.
View is designed as a virtual table that is extracted from a database.	Table is designed with a limited number of columns and unlimited number of rows.
View can incorporate several tables into one virtual table.	Multiple tables are needed to store linked data and records.
View is used to query certain data contained in several different tables.	Tables holds basic user data and holds instances of a defined object.

Create an VIEW :

Syntax :

1. CREATE VIEW :

CREATE VIEW view_name AS

SELECT column(s)

FROM table_name

WHERE condition;

2. SELECT VIEW :

SELECT * FROM VIEW

Create a View for Suppliers list :

Create a view for Items list :

How to create a backup to the database :

A. Using Query :

B. Using SSMS :

1. Open SQL Server management studio.

2. In Object explorer connect to an instance of the sql server database engine and then expand the instance.

3. Expand database and then right click our selected database to backup.

4. Go to tasks and create backup then one window will be opened.

5. To choose path

6. To set file name

7. Click OK

7. The Database is successfully completed

8. Location of backup file

Restore within the database :

A. Using Query :

B. Using UI : 1. Open SQL Server management studio. 2. In Object explorer connect to an instance of the sql server database engine and then expand the instance. 3. Expand database and then right click our selected database to restore. 4. Go to tasks and create restore then one window will be opened. 5. To click Device.