Sql Queries

Drop Column: -----------select * from food ALTER TABLE food DROP COLUMN Cutlet, SwetCorn

Execute Procedure useing output paramerter: -------------------------------------------drop proc testop Create proc testop @i_a int, @i_b int, @i_c int outPUT, @i_d int outPUT AS Begin PRINT @i_a PRINT @i_b Set @i_c = @i_a + @i_b set @i_d = @i_c --return @i_c --return @i_d end Declare @i_a as int Declare @i_b as int Declare @i_c as int Declare @i_d as int SET @i_a = 1 SET @i_b = 3 EXEC testop @i_a, @i_b, @i_c OUT, @i_d OUT SELECT @i_c SELECT @i_d

Second highest salary: ---------------------SELECT Distinct(a.salary) From Salary A Where 2 =( Select COUNT(distinct(Salary)) FROM salary b WHERE a.salary <= b.salary )

Tables: ---------Studernt

Test

Marks

StudID PK S-Name

TestID PK T-Name T-Date

MarkID PK TestID FK StudID FK Marks

Select S-Name from student where studID in ( Select StudID from marks where marks = ( Select max(Marks) from MArks ))

Note: -----

• • • • • •

Strings should be in 'single quotes'; We can use mathematical and string expressions as well as field names and constants. We use AND to ensure that two or more conditions hold true. The word IN allows us to check if an item is in a list. The word LIKE permits pattern matching - % is the wildcard. BETWEEN allows range checking - note that it is inclusive.

Round(): --------

The function ROUND(x,p) will round the value x to p decimal places. Eg:

ROUND(population/1000000,0)

select * from AT_Student select * from AT_Student where S_Student_Name LIKE 'd%' ORDER BY: --------select S_FatherName from AT_Student ORDER BY S_FatherName select S_FatherName from AT_Student ORDER BY S_FatherName DESC AND OR: ------select S_Student_Name from AT_Student where S_Student_Name = 'Prakash' AND S_FatherName = 'Kumar' select S_Student_Name from AT_Student where S_Student_Name = 'Prakash' OR S_FatherName = 'Kumar' IN: --select S_Student_Name,S_FatherName from AT_Student where S_Student_Name IN ('Prakash', 'BALAMURUGAN') BETWEEN AND: -----------select S_Student_Name from AT_Student where S_DateofJoining BETWEEN '1/1/2005' AND '12/31/2005' select S_Student_Name from AT_Student where S_DateofJoining NOT BETWEEN '1/1/2005' AND '12/31/2005' AS (Column Alias): -----------------select S_Student_Name AS Name , S_FatherName AS FtherName from AT_Student AS (Table Alias): ----------------select S_Student_Name, S_FatherName from AT_Student AS StudentDetails select * from AT_Student select * from AT_Student_Registration Join: ----select AT_Student.S_Student_Name , AT_Student_Registration.SR_RegistrationYear from AT_Student, AT_Student_Registration where AT_Student.S_Student_ID = AT_Student_Registration.SR_Student_ID select AT_Student.S_Student_Name from AT_Student, AT_Student_Registration where AT_Student.S_Student_ID = AT_Student_Registration.SR_Student_ID AND SR_RegistrationYear = '2006' INNER JOIN: -----------

-----

SELECT field1, field2, field3 FROM first_table INNER JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield

select S_Student_Name, SR_RegistrationYear from AT_Student INNER JOIN AT_Student_Registration ON AT_Student.S_Student_ID = AT_Student_Registration.SR_Student_ID -- The INNER JOIN returns all rows from both tables where there is a match. -- If there are rows in Employees that do not have matches in Orders, those rows will not be listed. LEFT JOIN: ----------SELECT field1, field2, field3 -FROM first_table -LEFT JOIN second_table -ON first_table.keyfield = second_table.foreign_keyfield select S_Student_Name, SR_RegistrationYear from AT_Student LEFT JOIN AT_Student_Registration ON AT_Student.S_Student_ID = AT_Student_Registration.SR_Student_ID -- The LEFT JOIN returns all the rows from the first table (Employees), -- even if there are no matches in the second table (Orders). -- If there are rows in Employees that do not have matches in Orders, those rows also will be listed. RIGHT JOIN: -----------SELECT field1, field2, field3 -FROM first_table -RIGHT JOIN second_table -ON first_table.keyfield = second_table.foreign_keyfield select S_Student_Name, SR_RegistrationYear from AT_Student RIGHT JOIN AT_Student_Registration ON AT_Student.S_Student_ID = AT_Student_Registration.SR_Student_ID -- The RIGHT JOIN returns all the rows from the second table (Orders), -- even if there are no matches in the first table (Employees). -- If there had been any rows in Orders that did not have matches in Employees, -- those rows also would have been listed. UNION: ------ The UNION command is used to select related information from two tables, -- much like the JOIN command. However, -- when using the UNION command all selected columns need to be of the same data type.

select S_Student_Name from AT_Student UNION select SR_Faculty_ADSID from AT_Student_Registration select * from AT_Student select * from AT_Student_Registration Create a Table: ---------------CREATE TABLE table_name(column_name1 data_type,column_name2 data_type,.......) USE KUMS CREATE TABLE Person (LastName varchar,FirstName varchar,Address varchar,Age int) CREATE TABLE PersonLength (LastName varchar(30),FirstName varchar,Address varchar,Age int) INDEX: ------CREATE UNIQUE INDEX index_name ON table_name (column_name) -CREATE INDEX index_name ON table_name (column_name) CREATE INDEX PersonIndex ON Person (LastName) CREATE INDEX PersonIndex ON Person (LastName DESC) CREATE INDEX PersonIndex ON Person (LastName, FirstName) DROP : ----DROP INDEX table_name.index_name DROP TABLE table_name DROP DATABASE database_name Truncate a Table: ----------------What if we only want to get rid of the data inside a table, and not the table itself? -Use the TRUNCATE TABLE command (deletes only the data inside the table): TRUNCATE TABLE table_name ALTER TABLE: ------------The ALTER TABLE statement is used to add or drop columns in an existing table. -- ALTER TABLE table_name ADD column_name datatype ALTER TABLE table_name DROP COLUMN column_name

ALTER TABLE Person ADD City varchar(30) ALTER TABLE Person DROP COLUMN Address Built-in Aggregate functions: ----------------------------AVG(column), COUNT(column), COUNT(*), COUNT(DISTINCT column), FIRST(column), LAST(column), MAX(column), MIN(column), SUM(column) --

SELECT column,SUM(column) FROM table GROUP BY column

Group by: ---------SELECT Company,SUM(Amount) FROM Sales GROUP BY Company HAVING: -------HAVING was added to SQL because the WHERE keyword could not be used against -aggregate functions (like SUM), and without HAVING... -it would be impossible to test for result conditions. -SELECT column,SUM(column) FROM table GROUP BY column HAVING SUM(column) condition value -SELECT Company,SUM(Amount) FROM Sales GROUP BY Company HAVING SUM(Amount)>10000 The SELECT INTO Statement: --------------------------The SELECT INTO statement is most often used to create backup copies of tables -or for archiving records. -SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source VIEW: -----A view is a virtual table based on the result-set of a SELECT statement. -CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition

select * from AT_Student Display second row value: ------------------------select top 1 * from (

SELECT TOP 7 * FROM AT_Student ORDER BY S_STUDENT_id )z ORDER BY S_STUDENT_id DESC

select max(S_Student_ID) from (select top 7 S_Student_ID from AT_Student order by S_Student_ID)z

CurrentDate Retrieve: --------------------SELECT CONVERT(VARCHAR(10),GETDATE(),103) AS [Date] SELECT GETDATE() as [Data & Time] emp_hire_date datetime DEFAULT GETDATE()

drop table #DateValue CREATE TABLE #DateValue ( drpdwnDate DATETIME ) DECLARE @i_DATE AS DATETIME DECLARE @l_CurrentDATE AS VARCHAR(20) DECLARE @l_FinalDateVal AS DATETIME DECLARE @i_Index AS INT SET @i_DATE = '06/30/2006' SET @i_Index = 0 IF UPPER(DATENAME(dw, @i_DATE)) = 'MONDAY' BEGIN SET @l_CurrentDATE = CONVERT (VARCHAR(20), @i_DATE, 101) END ELSE BEGIN SET @l_CurrentDATE = CONVERT(VARCHAR(20), @i_DATE + (((7 - DATEPART(dw, @i_DATE) + 2)) % 7), 101) END SET @l_FinalDateVal = CAST(@l_CurrentDATE AS DATETIME) WHILE @i_Index < 8 BEGIN

IF @i_Index <> 0 BEGIN SET @l_FinalDateVal = DATEADD(dd, 7, @l_FinalDateVal ) END INSERT INTO #DateValue (drpdwnDate) VALUES (@l_FinalDateVal) SET @i_Index = @i_Index + 1 END SELECT CONVERT(VARCHAR(10), drpdwnDate, 103) AS DateValue FROM #DateValue