SQL
Tutorials
Part -3
Customer Table –Design (Add Same Field in CustomerTable)
Customer Table: with Data (Add Same Data in Customer Table)
NEXT QURIES
/* Between Dates */
/* The following SQL statement selects all orders with an OrderDate BETWEEN 01/01/2018 to 02/02/2021 */
select * from Customer where OrderDate Between '01/01/2018' AND '02/02/2021'
/*The SELECT TOP clause is used to specify the number of records to return */
select top 3* from Customer
Select top 3 percent* from Customer
select top 3* from Customer where FirstName= 'atul' /* top with where condition*/
/* The MIN() function returns the smallest value of the selected column.
The MAX() function returns the largest value of the selected column.*/
/* MIN() Syntax
SELECT MIN(column_name)
FROM table_name
WHERE condition;
MAX() Syntax
SELECT MAX(column_name)
FROM table_name
WHERE condition;*/
select MIN (OrderAmount) as SamllestPrice from Customer
select MAX (OrderAmount) from Customer
/* 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
*/
/* SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;
*/
/* The following SQL statement selects all customers with a FirstName starting with "A":*/
select * from Customer where FirstName like 'A%';
/* The following SQL statement selects all customers with a LastName ending with "h":*/
select * from Customer where LastName like '%h';
/* The following SQL statement selects all customers with a Address that have "u" in any position: */
select * from Customer where LastName like '%u%';
/*The following SQL statement selects all customers with a FirstName that starts
with "a" and are at least 3 characters in length: */
SELECT * FROM Customer
WHERE FirstName LIKE 'a__%';
/* The following SQL statement selects all customers with a FirstName that
starts with "a" and ends with "l":*/
SELECT * FROM Customer
WHERE FirstName LIKE 'a%l';
/* The following SQL statement selects all customers with a FirstName
that does NOT start with "a":*/
SELECT * FROM Customer
WHERE FirstName NOT LIKE 'a%';
/* A stored procedure is a prepared SQL code that you can save, so the code can be
reused over and over again.
So if you have an SQL query that you write over and over again, save it as a stored
procedure, and then just call it to execute it.
You can also pass parameters to a stored procedure, so that the stored procedure can
act based on the parameter value(s) that is passed. */
/* Stored Procedure Syntax
CREATE PROCEDURE procedure_name
AS
sql_statement
GO;
Execute a Stored Procedure
EXEC procedure_name;
*/
/* The following SQL statement creates a stored procedure named "SelectAllCustomers"
that selects all records from the "Customer" table:*/
Create Procedure Selectallcustomer
as
select *from Customer
go
exec Selectallcustomer /* for Execute stored Procedure */
/* store Procure with parameter */
CREATE PROCEDURE SelectAllCustomers
@Country nvarchar(30)
AS
SELECT * FROM Customer WHERE FirstName = @FirstName
GO
exec SelectAllCustomers @FirstName = 'atul'
/*Setting up multiple parameters is very easy. Just list each parameter
and the data type separated by a comma as shown below. */
/*The following SQL statement creates a stored procedure that selects FirstName,
LastName, And Age from Customer table*/
Create Procedure selectData @FirstName nvarchar(10), @LastName nvarchar(10), @Age nvarchar(10)
as
select *from Customer where FirstName = @FirstName AND LastName = @LastName AND Age = @Age
Exec selectData @FirstName ='Atul' , @LastName = 'vish' , @Age = '25'
/* update : It is the WHERE clause that determines how many records will be updated. */
update Customer set FirstName = 'rajeev' where FirstName = 'Anand'
/* A wildcard character is used to substitute one or more characters in a string.
Wildcard characters are used with the SQL LIKE operator. The LIKE operator is used
in a WHERE clause to search for a specified pattern in a column.*/
select * from Customer where Address like 'P%'
select * from Customer where Address like '%d%'
select * from Customer where Address like 'peris'
/* The following SQL statement selects all customers with a Address starting with "L", "P", or "A":*/
select * from Customer where Address like '[LPA]%'
/* The following SQL statement selects all customers with a Address starting with "a", "b", or "c":*/
select *from Customer where Address like '[a-c]%'
/* The following SQL statement selects all customers with a Address Not starting with "L", "P", or "A":*/
select * from Customer where Address not like '[LPA]%'
/*
The SQL IN Operator
The IN operator allows you to specify multiple values in a WHERE clause.
The IN operator is a shorthand for multiple OR conditions.
IN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
or:
SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);
*/
select *from Customer where Country in ('London')
select * from Customer where FirstName Between 'atul' and 'Naresh'
/*n 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
*/
/* Copy all columns from one table to another table:
INSERT INTO table2
SELECT * FROM table1
WHERE condition;
*/
/* Copy only some columns from one table into another table:
INSERT INTO table2 (column1, column2, column3)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;
*/
insert into Orderbackup (OrderAmount,OrderDate) select OrderAmount , OrderID from Orders
Comments
Post a Comment