sqlserver basic interview questions part 9

VARP(column)


Scalar functions


Scalar functions operate against a single value, and return a single value based on the input value.Useful Scalar Functions in MS Access


Function Description


UCASE(c) Converts a field to upper case


LCASE(c) Converts a field to lower case


MID(c,start[,end]) Extract characters from a text field


LEN(c) Returns the length of a text field


INSTR(c) Returns the numeric position of a named character within a text field


LEFT(c,number_of_char) Return the left part of a text field requested


RIGHT(c,number_of_char) Return the right part of a text field requested


ROUND(c,decimals) Rounds a numeric field to the number of decimals specified


MOD(x,y) Returns the remainder of a division operation


NOW() Returns the current system date


FORMAT(c,format) Changes the way a field is displayed


DATEDIFF(d,date1,date2) Used to perform date calculations


SQL GROUP BY and HAVING


Aggregate functions (like SUM) often need an added GROUP BY functionality.


GROUP BY...


GROUP BY... was added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called, and without the GROUP BY function it was impossible to find the sum for each individual group of column values. The syntax for the GROUP BY function is:SELECT column,SUM(column) FROM table GROUP BY column


GROUP BY Example


This "Sales" Table:Company Amount


W3Schools 5500


IBM 4500


W3Schools 7100 And This SQL:SELECT Company, SUM(Amount) FROM Sales Returns this result:Company SUM(Amount)


w3Schools 17100


 IBM 17100


 W3Schools 17100 The above code is invalid because the column returned is not part of an aggregate. A GROUP BY clause will solve this problem: SELECT Company,SUM(Amount) FROM Sales


 GROUP BY Company Returns this result:Company SUM(Amount)


 W3Schools 12600


 IBM 4500


 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. The syntax for the HAVING function is:SELECT column,SUM(column) FROM table




GROUP BY column


 HAVING SUM(column) condition value This "Sales" Table:Company Amount


 W3Schools 5500


 IBM 4500


 W3Schools 7100 This SQL:SELECT Company,SUM(Amount) FROM Sales


 GROUP BY Company


 HAVING SUM(Amount)>10000 Returns this resultCompany SUM(Amount)


 W3Schools 12600


 SQL The SELECT INTO Statement


 The SELECT INTO Statement


 The SELECT INTO statement is most often used to create backup copies of tables or for archiving records.Syntax


SELECT column_name(s) INTO newtable [IN externaldatabase]


 fROM source


Make a Backup Copy


The following example makes a backup copy of the "Persons" table:SELECT * INTO Persons_backup


FROM Persons The IN clause can be used to copy tables into another database:SELECT Persons.* INTO Persons IN 'Backup.mdb'


FROM Persons If you only want to copy a few fields, you can do so by listing them after the SELECT statement:SELECT LastName,FirstName INTO Persons_backup


FROM Persons You can also add a where clause. The following example creates a "Persons_backup" table with two columns (FirstName and LastName) by extracting the persons who lives in "Sandnes" from the "Persons" table:SELECT LastName,Firstname INTO Persons_sandnes


FROM Persons


WHERE City='Sandnes' Selecting data from more than one table is also possible. The following example creates a new table "Empl_Ord_backup" that contains data from the two tables Employees and Orders:SELECT Employees.Name,Orders.Product


INTO Empl_Ord_backup


 fROM Employees


 INNER JOIN Orders


 ON Employees.Employee_ID=Orders.Employee_ID