Chapter 1: Introduction:

1.What is Data
Data is nothing but a collection of raw facts which doesn’t give the exact information.
2.What is Database
Database is nothing but a collection of information or object that is organized so that it can be easily accessed, manipulated and updated.
3.What is DBMS
A DBMS is a software package designed to define, manipulate, retrieve and manage data in database. Data is stored in the form of files. It doesn’t support distributed database.
4.What is RDBMS
Relational Database Management System is an advanced version of a DBMS system. This system also allows us to access data more efficient than DBMs.

Some examples of relational database management system:

Oracle Database, MySQL, Microsoft SQL Server, and IBM DB2.

DIfferent Types of RDBMS
1.MySql
MySql is an open source SQL database which is developed by Microsoft inc.
2.Oracle
It is a very large multi user based database management system. Oracle is a relational database management system developed by ‘Oracle Corporation’.
3.MS SQL Server
Microsoft SQL Server is a relational database management system (RDBMS) that supports a wide variety of transaction processing, business intelligence and analytics applications in corporate IT environments. It is used to develop backend applications. It is a pure platform dependent it works only in windows operating system.
4.Sybase
Sybase is a computer software company that develops and sells database management system (DBMS) and middleware products. Sybase was the first enterprise DBMS for the Linux operating system.
5.MS Access
Microsoft Access is a Database Management System (DBMS) from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software development tools. It is a member of the Microsoft Office suite of applications, included in the professional and higher editions.
6.IBM DB2
A Db2 server is a relational database management system (RDBMS) that delivers data to its IBM data server clients. ... An IBM data server client is an application where you can run commands and SQL statements against a Db2 server, connect to a remote Db2 server, and access its databases.

Chapter 2:

Download and Install SQL Server: Theory class >> kiran will explain only theory but you need to download and install the sql

server latest version

Installation steps for ms sql server

Step 1: Install MS SQL Server.

Follow the steps below to install MS SQL Server (version 2008 or later).

1. Download and run the MS SQL Server Installer.

2. In the SQL Server Installation Center window, select Installation in the navigation bar to the left; then select New SQL Server

stand-alone installation or add features to an existing installation.

3. When the SQL Server Setup window appears, enter your product key on the Product Key page. Then select Next.

4. In the License Terms window, accept the license terms and then select Next.

5. In the Microsoft Update window, if you wish to have SQL Server updated automatically, select Use Microsoft Update to check for updates. Otherwise,

leave the box blank. Then select Next.

6. In the Install Rules window, correct any rules with a Failed status and note any Warnings. Then select Next.

7. In the Feature Selection window, select Database Engine Services. (You may leave Instance root directory at the default.) Then select Next.

8. In the Instance Configuration window, you may leave Default Instance selected, unless you wish to name this SQL Server instance something other than

MSSQL SERVER. Then select Next.

9. Leave the defaults in the Server Configuration window, and select Next.

10. When the Database Engine Configuration window opens, follow these steps in the Server Configuration tab:

a. In Authentication Mode, select Mixed Mode. Enter and confirm a password for the system administrator (“sa”) account.

b. In Specify SQL Server administrators, select either Add Current User or Add if you wish to add a different user.

c. Then select Next.

11. In the Ready to Install window, select Install.

12. When the Complete window appears, select Close.

Step 2: Set up your database with MS SQL Server Management Studio.

1. Return to the SQL Installation Center window and select Install SQL Server Management Tools.

2. Your browser will launch and take you to the Download SQL Server Management Studio web page. Select the Download SQL Server Management Studio link

3. When the Microsoft SQL Server Management Studio welcome page appears, select Install.

4. Wait for the installation to complete, and then select Close to close the window.

5. Open Management Studio in Windows by navigating to Start→All Programs/All Apps→Microsoft SQL Server→Microsoft SQL Server Management Studio.

6. Management Studio will launch. When the Connect to Server dialog box appears:

a. Select SQL Server Authentication in the Authentication drop-down menu.

b. Enter “sa” (meaning system administrator) in Login.

c. Enter the password you created earlier in the process in Password.

d. Select Connect.

7. In the Object Explorer bar to the left, right-click on Security; select New; then select Login

8. In the Login – New window:

a. Enter a new login name. In our example, we are using “ITManager.”

b. Select SQL Server authentication, and then enter and confirm a password for this user.

c. UNcheck Enforce password policy—the box should be blank.

d. Select the Server Roles tab in the navigation bar to the left, and choose the permissions you wish to grant to the user. The

minimum permissions Device ManageR requires are public, which cannot be unchecked, and sysadmin.

e. Then select OK at the bottom of the screen.

9. In the Object Explorer bar to the left, right-click on Databases, and select New Database from the menu that appears.

10. In the New Database window, enter a name for the database Device ManageR will use. (We’re calling ours “DeviceDB” in our example.) Then select OK

at the bottom of the screen.

1.Sql authentication
a.Windows
Windows authentication is the default, and is often referred to as integrated security because this SQL Server security model is tightly integrated with Windows. Specific Windows user and group accounts are trusted to log in to SQL Server. Windows users who have already been authenticated do not have to present additional credentials.
b.Mixed(Sql Server) >> Keep password as sps
Mixed mode supports authentication both by Windows and SQL Server. Username and password pairs are maintained within SQL Server.
2.How to connect to the sql server remotely
First open the sql server management studio.
Right click on your server name and then go to properties.

Click the checkbox Allow remote connections to this server.

3.Create a new user to sql server
Open sql server management studio.
Connect to the sql server database where you want to create a login.
Open the security folder.
Right click on the login folder and then click on new login.

If you want to assign rights to a windows account, select windows authentication. If you want to create an account that exists

only in the database, select sql server authentication.

Provide the login name in the text box. You can use the browse button to select an existing account if you choose windows

authentication.
If you choose sql server authentication, you must also provide a strong password in both the password and confirmation text boxes.
Customize the default database and default language for the account, If desired, using the dropdown boxes at the bottom of the window.

Click ok to create the account.
4.Create a new user to only specific database
A user is an account that you can use to access the sql server

Create User using SQL Server Management Studio:Connect to SQL Server then expand the Databases folder from the Object Explorer.
Identify the database for which you need to create the user and expand it.
Expand its Security folder.
Right-click the Users folder then choose "New User…"

You will get the following screen,

Enter desired username.
Enter the loginname.
Click ok.
5.Where the sql files are stored
When you create a database in sql server one of the settings it will make by default will be creation of data file .mdf and .ldf.

These are the two files will store the data physically in bits or bytes.
6.MDF and LDF
When we create a database by default mdf and ldf files will be created to store the data.

MDF: MDF’s files are where all the database information is stored. It contains the rows,

columns, fields and data created by the administrator or user.

LDF: During various creation and modification processes within the MDF, all activity is

logged in the LDF.

7.Port number: A port number is a way to identify a specific process to which an Internet or other network message is to be

forwarded when it arrives at a server. For the Transmission Control Protocol and the User Datagram Protocol, a port number is a 16-bit integer that is put in the header appended to a message unit.

Chapter 4:Database: Output: Create a database for your college

1.What is Database
A database is a collection of information that is organized so that it can be easily accessed, managed and updated.
2.Syntax; Create using command statement
C:\Users\TM-135\Downloads>sqlcmd -S DESKTOP-T06U81T\NAGESHSQL -E

The –S value is to specify the SQL Server name of the instance and -E is to specify a

trusted connection. If you do not specify the sql server name, it will try to connect to the

local machine.

Output:

3.Create database using management studio
Open sql server management studio and then open databases folder click on new database and enter the database name in

text field.

4.Drop/Delete the created database from command and MS

Dropping database from the management studio:

Connect to the Database Engine.
From the Standard bar, click New Query.
Create the query (drop database db-name).

Dropping database from the cmd:

Chapter 5: Sql Data Types: The data type of a column defines what value the column can hold: integer, character, money, date and time,

binary, and so on.

Each column in a database table is required to have a name and a data type.

An SQL developer must decide what type of data that will be stored inside each column when creating a table. The data type is a

guideline for SQL to understand what type of data is expected inside of each column, and it also identifies how
SQL will interact with the stored data.

1.Number
a.Int: Allows whole numbers between -2,147,483,648 and 2,147,483,647 and the storage is 4 bytes.
b.Decimal: decimal(p,s) Fixed precision and scale numbers.Allows numbers from -10^38 +1 to 10^38 –1.The p

parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0 and the storage is 5 -17 bytes.
c.Numeric: numeric(p,s) Fixed precision and scale numbers.Allows numbers from -10^38 +1 to 10^38 –1.The p

parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0 and the storage is 5 -17 bytes.
d.Float: Floating precision number data from -1.79E + 308 to 1.79E + 308.

The n parameter indicates whether the field should hold 4 or 8 bytes. float(24)

holds a 4-byte field and float(53) holds an 8-byte field. Default value of n is 53.

2.String
a.Varchar: varchar(n) defines variable width character string. Maximum size is 8000 characters.
b.Nvarchar: nvarchar() Variable width character string. Maximum size is 4000 characters.
c.Text: Variable width character string. Maximum size is 2GB of text data.

3.Date: Store a date only. From January 1, 0001 to December 31, 9999 and the storage is 3 bytes.
4.Xml: Stores XML formatted data. Maximum 2GB.

Chapter 6 Table: Create tables for students, teachers,

1.What is table
In relational databases, and flat file databases, a table is a set of data elements(values) using a model of vertical columns

(identifiable by name) and horizontal rows. A table has a specified number of columns, but can have any number of rows.
2.Syntax; Create using command statement

Create table table_name(col1 datatype,col2 datatype, col3 datatype, ……..)

Eg: create table Students(ID int primary key,Name varchar(20),MobileNumber

varchar(20),City varchar(20))

How to view the created table in command;

Select name from sys.tables

3.Create Table using MS
Click on new query and then write the query you want and execute.

create table Students(ID int primary key,Name varchar(20),MobileNumber

varchar(20),City varchar(20));

4.Design the students and teachers with real time attributes.

Query:

create table StudentDetails(StudentId int,Name varchar(20),Marks int,City varchar(20));
Insert into StudentDetails values(100,’Nagesh’,80,’Hyderabad’);

5.SELECT * FROM [Student_Marks]

Select *from StudentDetails;

6.Truncate table: TRUNCATE TABLE statement is used to delete the data inside the table not the structure of the table.

Syntax:

Truncate table table_name;

Eg:

Truncate table StudentDetails;

Chapter 7:Alter: Add new column, change a column name, change the datatype of a column and increase the size of any column

1.Add new column: Alter command is used to add a new column to the table.

Syntax: alter table table_name add column_name datatype;

Eg:

Alter table StudentDetails add MobileNumber varchar(20);

2.Drop Column: By using alter and drop commands we can drop one of column from the table.

Eg: alter table StudentDetails drop column City;

3.Change the column data type: Follow the below syntax to change the column datatype

Syntax:

Alter table StudentDetails alter column MobileNumber char(20);

4.Change the column size:

Syntax:

alter table StudentDetails alter column MbNo varchar(50);

5.Change column name:

Eg:

EXEC sp_rename 'StudentDetails.MobileNumber','MbNo','column';

Chapter 8: Constraints: Add all these constraints to your students and teachers table..

Create primary key for students and teachers tables

Sql constraints are used to specify the rules to the data in table.

Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the

ALTER TABLE statement.

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the

table. If there is any violation between the constraint and the data action, the action is aborted.

Syntax:

CREATE TABLE table_name (

column1 datatype constraint,

column2 datatype constraint,

column3 datatype constraint,

....

);

1.Cell default value = null: Ensures that a column can have null values if we don’t give any value by default null value will be

assigned.
2.Not NULL: Ensures that a column can’t have a null value.

Syntax:

Create table student1(id int not null,name varchar(20),address varchar(50));

Alter table student add not null(id);

Output:

3.Unique: Ensures that all values in a column are different. There may be more than one unique key is possible in a table. This

can contain null values.

Syntax:

Create table customer(mobileno varchar(20)unique,name varchar(20));

Alter table customer add unique(mobileno);

Output:

Insert into customer values(‘9550076770’,’Nagesh’);

4.Check: Ensures that all values in a column satisfies a specific condition. When a wrong value is entered check will enforces

you to enter value again.

Create table hello(name varchar(20),age int check(age>=18));

In the above case if we enter age is less than 18 it shows error until we enter correct

value.

Output:

5.Primary key: A combination of a unique and not null. Uniquely identifies each row in a table. It doesn't allow null and

duplicate values.

Create table hello1(name varchar(20),id int primary key);

Output: W

6.Foreign key: Uniquely identifies a row/record in another table
7.Default: You can set a default value to a column when a common value is repeated more times.

Create table driver(dno int, dname varchar(20),address varchar(20)default ‘Hyderabad’);

Output:

8.Candidate Key: A candidate is a column, or set of columns in a table that can uniquely identify any database record

without referring to any other data. Each table may have one or more candidate keys, but one candidate key is unique, and it is called the primary key.
9.Composite key: Composite key, or composite primary key, refers to cases where more than one column is used to specify

the primary key of a table. In such a case, all foreign will also need to include all columns in the composite key.
10.Auto Increment: Auto-increment allows a unique number to be generated automatically when a new record is inserted

into a table. The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature.

CREATE TABLE Persons (

Personid int IDENTITY(1,1) PRIMARY KEY,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int);

Output:

Chapter 9: Insert: Insert 10 records in students table and 5 records in teachers table

1.Insert: Insert keyword is used to add data into database.

Syntax:

Insert into table_name values(col1 value,col2 value…...);

2.nsert all values without values keyword

Eg:

insert into Student(ID,Name,CollegeName,Branch,Address)

select

100 as ID,'Nagesh' as Name,'Gurunanak' as CollegeName,'EEE' as

Branch,'Hyderabad' as Address;

Output:

3.Insert only specific values: By the following we can insert the data only for the specific columns.

Eg:

Output:

4.Insert with random order: By the following way we can insert the data in random order that we have to mention in the

table name.

Eg:

Output:

5.Insert null values: To insert null values into the data we have to mention the value as null.

Eg:

Output:

6.Update: update the students records
While updating when there is no where condition then all the data will be updated with a single value.

Eg:

Output:

7.Where Update with condition >>
When we use where condition while updating only that particular data will be updated.

Eg:

Output:

8.Delete: Delete the record from table
If we want to delete all the record from the data then we should go with delete keyword without any condition.

Eg:

Output:

9.Delete with condition: When we use condition while deleting the data only that particular data will be deleted.

Eg:

Output:

Chapter 10: Select: Display data from students and teachers table

1.Select : The SELECT statement is used to select the data from the database.
2.SELECT all columns with *: Inorder to retrieve all the data in column we have to use the following method.

Eg:

Output:

3.Select specific columns: For retrieve the particular column data we have to follow the following method.

Eg:

Output:

4.Select with a where condition: For selecting the data by using condition will give that particular data of the row.

Eg:

Output:

5.Select using IN: The IN operator allows you to specify multiple values in a WHERE clause.

Actual data:

Output:

6.Select using NOT IN: The following statement is used to display the details of student table those are not in given

values.

Eg:

Output:

7.Select using like: The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards often used in conjunction with the LIKE operator:

% - The percent sign represents zero, one, or multiple characters
_ - The underscore represents a single character

Actual data:

Output:

8.Select using NOT like: This is exactly opposite to the like keyword.

Actual data:

Eg:

Output:

9.Select using between: The BETWEEN operator selects values within a given range. The values can be numbers, text,

or dates.

Syntax:

SELECT column_name(s)FROM table_name

WHERE column_name BETWEEN value1 AND value2;

Actual data:

Eg:

Output:

10.Select distinct: This is used to retrieve the different values from the database. In this process the data will be displayed in

the order of what we have given to the columns.

Actual data:

Output:

11.Select top: The SELECT TOP clause is used to specify the number of records to return.

Actual data:

Eg:

Output:

12.Select by order: The ORDER BY keyword is used to sort the result-set in ascending or descending order. By default order is

ascending order.

Syntax:

SELECT column1, column2, ...

FROM table_name

ORDER BY column1, column2, ... ASC|DESC;

Actual data:

Output:

ORDER BY DESC Example:

Output:

ORDER BY Several Columns Example:

Output:

13.Alias names: SQL aliases are used to give a table, or a column in a table, a temporary name.

Actual data:

Output:

Chapter 11: Sql Operators

1.Arithmetic Operators: These operators are used to perform arithmetic operations.

Some of the operators in SQL

Eg:

Output:

2.Comparison:

Eg: = Operator:

Output:

Eg: > Operator:

Output:

Eg: < Operator:

Output:

Eg: <> Operator:

Output:

3.Logical: Some of the logical operators

Eg: AND operator

Output:

Eg: OR operator

Output:

Chapter 13: Sql Predefined Functions:

1.Numeric functions: Some of the numeric functions

Eg: AVG function

SELECT AVG(Price) AS AveragePrice FROM Products;

Output:

Eg: Count function

Output:

Eg: Floor function

Output:

Eg: Max function

Output:

2.Date function: SQL Server comes with the following data types for storing a date or a date/time value in the database:

Some of the date functions:

DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MI:SS
SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS
TIMESTAMP - format: a unique number

To display current time;

Output:

To add date:

Output:

To find date difference:

Output:

Eg:

Output:

Date Name function: The DATENAME() function returns a specified part of a date.

Output:

To find given date format is correct or not: If the given data format is correct then it returns 1 if not it returns 0.

Output:

3.String functions: Some of the string functions

ASCII function: It returns the ascii value of the first character.

Output:

Charindex() function: The CHARINDEX() function searches for a substring in a string, and returns the position.

Output:

Concat() function: The concat() function adds the two strings together.

Output:

Datalength() function: It returns the length of the string data.

Output:

Left() function: Extracts characters from the string.

Output:

Lower() function: It converts the function into lower case.

Output:

Replace() function: The REPLACE() function replaces all occurrences of a substring within a string, with a new substring.

Output:

Replicate() function: It repeats the string for specified number of times.

Output:

REVERSE() function: It reverse the given string and returns the value.

Output:

Substring() function: It extracts some string characters.

Output:

UNICODE() function: It returns the unicode of the first character in the string.

Output:

Chapter 14:Joins: insert responsible teacher for each student and join students and teachers

name and display the students responsible teachers name and phone number.

Actual Data:

Performing Inner Join on above two tables:

Eg:

Output:

1.What is joins
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
2.Why joins are important
Joins are very important, If you want to increase the performance of the database. These are used to fetch the data from two

or more tables in a database.
3.Without joins?
Joins are very important while dealing with databases. Without joins we can’t get the data from multiple tables. In that case
we have to maintain a single table for all the data due to this the selecting of data becomes very difficult.
4.Types of Joins
Here are the different types of joins in SQL
INNER JOIN: Return records that have matching values in both the tables.

LEFT OUTER JOIN: Returns all records from the left table, and returns the matched records from the right table.

RIGHT OUTER JOIN: Returns all the records from the right table, and returns the matched records from the left table.

FULL OUTER JOIN: Returns all the records when there is a match in either left or right table.

Chapter 15: Inner Join: Display orders with customer name

Eg:

Actual Data: Orders table

Actual Data: Customers table

Performing inner join on above two tables:

1.What is Inner Join
Return records that have matching values in both the tables.
2.When to Use >> in which case
An INNER JOIN is such type of join that returns all rows from both the participating tables where the key record of one table

is equal to the key records of another table. This type of join required a comparison operator to match rows from the participating tables based on a common field or column of both the tables.

Chapter 16: Left Join:Display all the orders with customer name

Actual Data: Orders table

Actual Data: Customers table

Performing left join on above two tables: To display all orders with customer names.

1.What is Left Join
Returns all records from the left table, and returns the matched records from the right table.
2.When to Use >> in which case
The LEFT JOIN clause allows you to query data from multiple tables. It returns all rows from the left table and the matching

rows from the right table. If no matching rows found in the right table, NULL are used.

Chapter 17: Right Join: Display all the Customers with orders

Actual Data: Orders table

Actual Data: Customers table

Note: I have added one more data please look over it change accordingly.

Performing right join on above two tables:

1.What is Right Join
Returns all the records from the right table, and matched records from the left table.
2.When to Use >> in which case
When we want to return all the records from the right table then we have to use the right join keyword.

Chapter 18: Full Join: Display all the customers and all the orders

Actual Data:Orders table

Note: I have added one more data into the above table please look over it and change accordingly.

Actual Data: Customers table

Note: I have added one more data please look over it change accordingly.

Performing full join on above two tables:

1.What is Full Join
The FULL OUTER JOIN keyword return all records when there is a match in left (table1) or right (table2) table records.
2.When to Use >> in which case
If we want to select all the data from more number of tables then we should go for full join. The full join returns the value when
there is matching data is present in both the tables.

Chapter 19: Self Join: Display customers from same country

1.What is Self Join
A self JOIN is a regular join, but the table is joined with itself. It is useful for querying hierarchical data or comparing rows
within the same table.
2.When to Use >> in which case
You use a self join when a table references data in itself. E.g., an Employee table may have a SupervisorID column that

points to the employee that is the boss of the current employee. It's basically used where there is any relationship between rows stored in the same table.

Chapter 20: Union:

1.Union
The UNION operator is used to combine the result-set of two or more SELECT statements. The union operator selects only

the distinct values from the tables.
Each SELECT statement within UNION must have the same number of columns.
The columns must also have similar data types.
The columns in each SELECT statement must also be in the same order

Example on union operator:

Actual Data: Customers table data

Suppliers table data:

Performing union operator on above two tables

2.Union ALL
The union all operator selects all the values including duplicate values.

Example on union all operator:

Actual Data:Customers table data

Suppliers table data:

Performing union all operator on above two tables

3.When to Use >> in which case
The SQL UNION operator is used to combine the result sets of 2 or more SELECT statements. It removes duplicate rows

between the various SELECT statements. Each SELECT statement within the UNION must have the same number of fields in the result sets with similar data types.

Chapter 21: Group By: Create a table for Departments in you college like.. ECE, EEE,IT,CSE..

And in teachers table assign the teachers to departments and get the departments wise teachers count

Group with having: When we group by particular column and it also satisfies the condition to display the data. If the condition is true the related data

will be displayed otherwise no data will be displayed.

Output:

Chapter 13: All sql Keywords

1.https://www.w3schools.com/sql/sql_ref_keywords.asp
ADD: The ADD command is used to add a column to the table.

Syntax: alter table table_name add coloumn_name(data type)

For adding constraint also we use add command.

Syntax: alter table table_name add constraint_name(column name)

Alter: The ALTER TABLE command adds, deletes, or modifies columns in a table.

Syntax for dropping a column:

Alter table table_name drop column column_name;

Syntax for changing data type of a column: The ALTER COLUMN command

Is used to change the data type of the column.

Alter table table_name alter column column_name(data type);

The ALL command returns true if all of the subquery values meet the condition.
The AND command is used with WHERE to only include rows where both conditions is true.

Eg:SELECT * FROM Customers

WHERE Country='Germany' AND City='Berlin';

The ANY command returns true if any of the subquery values meet the condition.
The ANY command returns true if any of the subquery values meet the condition. An alias name exists only for the

duration of query.
The ASC command is used to sort the data returned in ascending order.

Eg: SELECT * FROM Customers ORDER BY CustomerName ASC;

The BACKUP DATABASE command is used in SQL Server to create a full backup of an existing SQL database.
BACKUP DATABASE testDBTO DISK = 'D:\backups\testDB.bak';
The BETWEEN command is used to select values within a given range. The values can be numbers, text, or dates.
The CASE command is used is to create different output based on conditions.

Eg:

Actual Data:

Performing case keyword on above table:

The CHECK constraint limits the value that can be placed in a column.

Eg: If the condition is true then it allows us to enter the value otherwise it gives error.

The CREATE DATABASE command is used is to create a new SQL database.

Syntax: Create database database_name

The CREATE TABLE command creates a new table in the database.

Syntax: create table table_name(col1 datatype,col2 datatype,......);

Creating table using another table:

The EXEC command is used to execute a stored procedure.
The FOREIGN KEY constraint is a key used to link two tables together.
The GROUP BY command is used to group the result set (used with aggregate functions: COUNT, MAX, MIN, SUM, AVG).
The CREATE INDEX command is used to create indexes in tables (allows duplicate values).
Indexes are used to retrieve data from the database very fast. The users cannot see the indexes, they are just used to speed

up searches/queries.
The following SQL creates an index named "idx_lastname" on the "LastName" column in the "Persons" table:
CREATE INDEX idx_lastname ON Persons (LastName);

The DROP INDEX command is used to delete an index in a table.

DROP INDEX table_name.index_name;

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

INSERT INTO Customers (CustomerName, City, Country) SELECT SupplierName, City, Country FROM Suppliers;

The CREATE UNIQUE INDEX command creates a unique index on a table (no duplicate values allowed)

CREATE UNIQUE INDEX uidx_pid ON Persons (PersonID);

Chapter 14: Missing items

1.Order by: It is used to arrange the data in ascending or descending order. By default value is ascending.
2.Select into : The SELECT INTO statement copies data from one table into a new table.

3.Insert into select: The INSERT INTO SELECT statement copies data from one table and inserts it into another table.
INSERT INTO SELECT requires that data types in source and target tables match
The existing records in the target table are unaffected

INSERT INTO Customers (CustomerName, City, Country)SELECT SupplierName, City, Country FROM Suppliers;

4.Backup and its importance: The BACKUP DATABASE statement is used in SQL Server to create a full backup of an existing

SQL database.Online backup service is a method of data backup and storage in which a service provider handles the stored data. A backup service can help people and companies manage their data better. Most services offer encryption and protect the data from loss caused by technological malfunction or cybercrime.
5.Backup before any operation: Simply speaking, a backup is a copy of data. This copy includes important parts of your

database such as the control file and datafiles. A backup is a safeguard against unexpected data loss and application errors; should you lose your original data, you can use the backup to make it available again.
6.Sql Case: The CASE command is used is to create different output based on conditions.
7.NULL Function: The SQL Server ISNULL() function lets you return an alternative value when an expression is NULL:

SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0))

FROM Products;

8.PATINDEX:Return the position of a pattern in string.

Select patindex(‘%schools%’,’w3schools’)

9.Stuff: The STUFF() function deletes a part of a string and then inserts another part into the string, starting at a specified

position.

10.Comments: Single line comments start with --.

Multi-line comments start with /* and end with */.

a.Why to write comments:Comments are used to explain sections of SQL statements,

or to prevent execution of SQL statements.
b.Comments important: Comments are important because while entering or selecting data we can specify that what we

are going to do in this case.

Chapter 26: Sql Views: Create a view to get the product and supplier details

1.What is view and its importance:A view contains rows and columns, just like a real table. The fields in a view are fields from

one or more real tables in the database.
2.Difference between tables and views: A view consists of rows and columns just like a table. The difference between a view

and a table is that views are definitions built on top of other tables (or views), and do not hold data themselves. If data is changing in the underlying table, the same change is reflected in the view.

Chapter 27: Sql Commands

DDL: DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database

schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database.

Examples of DDL commands:

1.CREATE – is used to create the database or its objects (like table, index, function, views, stored procedures and triggers).
2.DROP – is used to delete objects from the database.
3.ALTER-is used to alter the structure of the database.
4.TRUNCATE–is used to remove all records from a table, including all spaces allocated for the records are removed.
DML: The SQL commands that deals with the manipulation of data present in the database belong to DML or Data
Manipulation Language and this includes most of the SQL statements.

Examples of DML:

1.INSERT – is used to insert data into a table.
2.UPDATE – is used to update existing data within a table.
3.DELETE – is used to delete records from a database table.

DCL: DCL includes commands such as GRANT and REVOKE which mainly deals with the rights, permissions and other

controls of the database system.

Examples of DCL commands:

GRANT-gives user’s access privileges to database.
REVOKE-withdraw user’s access privileges given by using the GRANT command.
TCL:TCL commands deals with the transaction within the database.

Examples of TCL commands:

COMMIT– commits a Transaction.
ROLLBACK– rollbacks a transaction in case of any error occurs.
SAVEPOINT–sets a savepoint within a transaction.
SET TRANSACTION–specific characteristics for the transaction.

Chapter 28: Backup and Restore

Take the backup of your current working database

Open SQL Server Management Studio Express and connect to the SQL server.
Expand Databases.
Right-click on the database you want to back up, then select Tasks > Back up. (This is not available for version 2019)
On the Back Up Database window, make sure the Database field contains the name of the database you want to back up.
Select the Backup Type. By default, it is Full - leave it set to that.
Click Remove to remove the default/last backup file name.
Click Add to open the Select Backup Destination window.
Click [...] next to the File Name field.
On the Locate Database Files window, select the folder where you want to backup file to go. By default, it is ..\Microsoft SQL

Server\MSSQL.1\MSSQL\Backup.
In the File Name field, type the name for this backup, with a .bak extension. For example, xyz_20080221.bak for a backup of the

XYZ database created on 21 February 2008.
Click OK to close the Locate Database Files window.
Click OK to close the Select Backup Destination window.
Click OK to start the backup. The progress icon displays in the lower left corner, and a ‘completed successfully’ message displays

when its done.

Chapter 29: Missing Items

1.Exception handling: Error handling in SQL Server give us control over Transact-SQL code. For example when things go

wrong we get a chance to do something about it and possibly make it right again.

Syntax:

BEGIN TRY

--code to try

END TRY

BEGIN CATCH

--code to run if error occurs

--is generated in try

END CATCH

2.Begin and End: END statement bounds a logical block of SQL statements. We often use the BEGIN...END at the start

and end of a stored procedure and function. ... However, the BEGIN...END is required for the IF ELSE statements, WHILE statements, etc., where you need to wrap multiple statements
3.Using [--] in where condition
4.Commit and Rollback: A COMMIT is a database command used in transaction management to save all changes made

to the transaction as final.

A ROLLBACK is a database command used in transaction management to

revert the previous changes on the transaction.

5.What is T-Sql: T-SQL (Transact-SQL) is a set of programming extensions from Sybase and Microsoft that add several

features to the Structured Query Language (SQL), including transaction control, exception and error handling, row processing and declared variables.
6.PRINT: 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. In case of SSMS the PRINT statement output is returned to the Messages window and in case applications PRINT statement output is returned as an informational error message to the client application.

7.Variables: In SQL Server (Transact-SQL), a variable allows a programmer to store data temporarily during the

execution of code.
8.IF.. ELSE: If the given condition is true it prints the result otherwise it prints the else part.
9.Else.. IF: If the given condition is false then it checks for the else if block then the condition is true it prints the else if block

otherwise it prints else block.
10.While: In general, you should use a for loop when you know how many times the loop should run. If you want the loop to

break based on a condition other than the number of times it runs, you should use a while loop.
11.Return: RETURN statement in SQL procedures. The RETURN statement is used to unconditionally and immediately

terminate an SQL procedure by returning the flow of control to the caller of the stored procedure. It is mandatory that when the RETURN statement is executed that it return an integer value.

SQL-07/10/2019

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

Chapter 27: Sql user defined function

1.Why and its use and importance
User-defined functions are subroutines made of one or more Transact-SQL statements that can be used to encapsulate

code for reuse. It takes zero or more arguments and evaluates a return value.
2.Function name and syntax

CREATE FUNCTION [schema_name.]function_name (parameter_list)

RETURNS data_type AS

BEGIN

statements

RETURN value

END

First, specify the name of the function after the CREATE FUNCTION keywords. The schema name is optional. If you

don’t explicitly specify it, SQL Server uses dbo by default.
Second, specify a list of parameters surrounded by parentheses after the function name.
Third, specify the data type of the return value in the RETURNS statement.
Finally, include a RETURN statement to return a value inside the body of the function.

Multiplication of table

3.Function parameters: A function accepts inputs in the form of parameters and returns a value. SQL Server comes with

a set of built-in functions that perform a variety of tasks. Of course, you could create a stored procedure to group a set of SQL statements and execute them, however, stored procedures cannot be called within SQL statements.
4.Different Types of functions
a.Scalar Function: A scalar function accepts zero or more parameters and return a single value.

Example

b.Table-Valued Functions: This type of functions is special because it returns a table variable that you can query the

results of a join with other tables.

Inline table valued function: It contains a single statement that must be a select statement. The result of the query becomes the return value of the

function. No need for a begin-end block in this function.

Example for table valued function:

Multi-Statement Table Valued Functions:

In this the sql statements enclosed in begin-end blocks. The return value is declared as a table includes full structure of the table

to be returned.

Example Queries:

1.Getting the list of employees with their department names

Output:

2.Getting the list employees with their department name and department heads name

Output:

3.Getting the list of employees whose salary is greater than their department avg salary

Output:

4.Getting the list of employees whos salary is greater than their department head salary

Output:

5.Getting max salary employee

Output:

6.Getting the list of employees whos salary is less than avg of all employees salary

Output:

7.Getting the list of employees whose salary is less than their department average salary

Output:

8.Country wise maximum salary

Output:

9.Country wise employee count

Output:

10.Getting the list of employees whose firstname first letter is equal to the lastname first letter

Output:

11.Getting the employee name whose name does not having vowels

Output:

12.Getting the list of employees whose first name length is greater than last name length

Output:

Bike Store Database

http://www.sqlservertutorial.net/load-sample-database/

Production.brands

Brand_id

brand_name

Production.categories

Category_id

Category_name

Production.products

Product_id

Product_name

Brand_id

Category_id

Model_year

list_price

production.stocks

Store_id

Product_id

quantity

Sales.customers

customer_id

First_name

Last_name

Phone

Street

City

State

zip_code

sales.orders

Order_id

Customer_id

Order_status

--Order status: 1 = Pending; 2 = Processing; 3 = Rejected; 4 = Completed

Order_date

Required_date

Shipped_date

Store_id

staff_id

sales.order_items

Order_id

Item_id

Product_id

Quantity

List_price

discount

sales.staffs

Staff_id

First_name

Last_name

Phone

Active

Store_id

manager_id

sales.stores

Store_id

Store_name

Phone

Street

City

State

Zip_code

Write a query to get all the products details with their brand name and category name

Output:

2.Write a query to know the current products with their stock/inventory details.

Output:

3.Write a query to get all the orders with customer name.

Output:

4.write a query to get all the orders with customer name and the store details and staff who is involved in that order

Output:

5.write a query to get the order details with item name and the price and extended details

Output:

6.write a query to get the total sale value by customer

Output:

7.write a query to get the total sale value by Item

8.Write a query to get all the staff details with the store and manager names

9.Write a query to know the present stock of the products

10.Write a query to know which store is made highest sales

11.Write a query to know which item is sold high

12.write a query to know which item is less quantity so that we can reorder that item

13.Write a query in which city we have the customers but no store

14.Write a query to know which brand item is more selling

15.Write a query to know which category item we are making more profit

Search This Blog

Nagesh's Blog

Nagesh

SQL

ORDER BY DESC Example:

ORDER BY Several Columns Example:

Actual Data:

Eg:

SQL-07/10/2019

Bike Store Database

Comments

Post a Comment