SQL Tutorial
Part -1
Objective
For learn all Sql which is required for any software
professional I have prepared it different parts
What we learn
We will learn all Sql Queries, how to use, what is use,
examples and execution.
What we need to prepare before learn it?
·
Download Sql Server
·
Basic knowledge
·
Create table
·
We need to create two tables “Customer” and
“Order”
·
Screenshot is attached to create tables (please
create same table with same data) so it will help you to execute queries and
you will learn easily with less time.
Customer Table
–Design (Add Same Field in Order Table)
First you need to create database.
Create table “Customer” with same fields.
Order Table: with Data (Add Same Data in Order Table)
Customer Table - Design (Add Same Field in Customer Table)
Customer Table: with Data (Add Same Data in Customer Table)
values
from the "Address" column in the "Customers" table:*/
/* it
will remove duplicacy record from table and give output*/
Select distinct Address from Customer;
/* The
following SQL statement lists the number of different (distinct) customer
countries:*/
Select Count(distinct Address) from Customer; /* NULL values
are not counted.*/
/*where
condition */
/*The SQL
WHERE Clause
The WHERE
clause is used to filter records.
The WHERE
clause is used to extract only those records that fulfill a specified
condition*/
/*Syntax
SELECT
column1, column2, ...
FROM
table_name
WHERE
condition Note: The WHERE clause is not only used in SELECT
statement,
it is also used in UPDATE, DELETE statement, etc.!*/
select FirstName, LastName from Customer where Address = 'Delhi' and FirstName = 'Anand';
select FirstName, LastName from Customer where OrderAmount >=100 ;
select FirstName, LastName from Customer where not FirstName = 'atul';
select FirstName, LastName from Customer;
/* The
SQL AND, OR and NOT Operators
The WHERE
clause can be combined with AND, OR, and NOT operators.
The AND
and OR operators are used to filter records based on more than one condition:
• The AND operator displays a record if all
the conditions separated by AND are TRUE.
• The OR operator displays a record if any
of the conditions separated by OR is TRUE.
AND
Syntax
SELECT
column1, column2, ...
FROM
table_name
WHERE condition1 AND condition2 AND condition3 ...;
OR Syntax
SELECT
column1, column2, ...
FROM
table_name
WHERE
condition1 OR condition2 OR condition3 ...;
NOT
Syntax
SELECT
column1, column2, ...
FROM
table_name
WHERE NOT condition; */
select *from Customer where Address = 'delhi' and (FirstName ='a%' or Age>25)
/* The SQL ORDER BY Keyword
The ORDER
BY keyword is used to sort the result-set in ascending or descending order.
The ORDER
BY keyword sorts the records in ascending order by default. To sort the records
in descending order, use the DESC keyword.
ORDER BY
Syntax
SELECT
column1, column2, ...
FROM
table_name
ORDER BY column1, column2, ... ASC|DESC;*/
Select *from Customer order by Address ASC;
Select *from Customer order by Address Desc;
select count(OrderAmount) from customer ;
select sum (OrderAmount) from Customer;
Select OrderAmount from Customer where OrderAmount in (100,50)
Select OrderAmount from Customer where OrderAmount not in (500,100,50)
Select OrderAmount from Customer where OrderAmount in (select OrderAmount from Customer)
/* The
SQL BETWEEN Operator
The
BETWEEN operator selects values within a given range. The values can be
numbers, text, or dates.
The
BETWEEN operator is inclusive: begin and end values are included. */
/*
BETWEEN Syntax */
/* SELECT
column_name(s)
FROM
table_name
WHERE column_name BETWEEN value1 AND value2; */
select OrderAmount from Orders where OrderAmount Between 50 and 800
/* NOT
BETWEEN Example
To display the OrderAmount outside the range of the previous example, use NOT BETWEEN:*/
select OrderAmount from Orders where OrderAmount not Between 50 and 800
/* The
SQL SELECT INTO Statement
The
SELECT INTO statement copies data from one table into a new table.
SELECT
INTO Syntax
Copy all
columns into a new table:
SELECT *
INTO
newtable [IN externaldb]
FROM
oldtable
WHERE
condition; */
/* when
you want to Copy only some columns into a new table:*/
/* SELECT
column1, column2, column3, ...
INTO
newtable [IN externaldb]
FROM
oldtable
WHERE
condition;*/
/* The
new table will be created with the column-names and types
as
defined in the old table. You can create new column names using
the AS
clause.*/
/* Note:
if you give only column name , then only that column get copied in new table */
/*The
following SQL statement copies only the orderAmount 50 record in new table
"OrderStatus".*/
SELECT * INTO OrderStatus
FROM Orders
WHERE OrderAmount = 250
/* The
following SQL statement copies data from more than one table into a new
table:*/
/* SELECT
INTO can also be used to create a new, empty table using the schema of another.
Just add
a WHERE clause that causes the query to return no data:*/
/* New empty table will be created with name "newtable".*/
SELECT * INTO newtable
FROM CustomerOrder
WHERE 1 = 0;
Comments
Post a Comment