simple queries

Question 1

select in descending order

Answer 1

SELECT Name
FROM Student
WHERE course IN [‘C1’, ‘C2’,C4]
ORDER BY Name DESC, course ASC

Question 2

group with count example

Answer 2

SELECT Speciality, Count ()
FROM Student
GROUP BY Speciality
HAVING COUNT () > 2

Question 3

Update example

Answer 3

UPDATE myDB.students
SET fname = ‘Captain’, lname = ‘America’
WHERE student_id =

Question 4

UNION

Answer 4

SELECT name FROM Students
UNION
SELECT name FROM Contacts;

Question 5

UNION with duplicates

Answer 5

SELECT name FROM Students
UNION ALL
SELECT name FROM Contacts;

Question 6

MINUS

Answer 6

SELECT name FROM Students
MINUS
SELECT name FROM Contacts;

Question 7

INTERSECT

Answer 7

SELECT name FROM Students
INTERSECT
SELECT name FROM Contacts;

Question 8

cursor

Answer 8

DECLARE @name VARCHAR(50) /* Declare All Required Variables */

DECLARE db_cursor CURSOR FOR /* Declare Cursor Name*/
SELECT name
FROM myDB.students
WHERE parent_name IN (‘Sara’, ‘Ansh’)

OPEN db_cursor /* Open cursor and Fetch data into @name */
FETCH next
FROM db_cursor
INTO @name

CLOSE db_cursor /* Close the cursor and deallocate the resources */
DEALLOCATE db_cursor

Question 9

alias example

Answer 9

SELECT A.emp_name AS “Employee” /* Alias using AS keyword /
B.emp_name AS “Supervisor”
FROM employee A, employee B / Alias without AS keyword */
WHERE A.emp_sup = B.emp_id;

Question 10

Truncate

Answer 10

TRUNCATE command is used to delete all the rows from the table and free the space containing the table.
TRUNCATE TABLE Candidates;

Question 11

Aggregate functions

Answer 11

AVG() - Calculates the mean of a collection of values.
COUNT() - Counts the total number of records in a specific table or view.
MIN() - Calculates the minimum of a collection of values.
MAX() - Calculates the maximum of a collection of values.
SUM() - Calculates the sum of a collection of values.
FIRST() - Fetches the first element in a collection of values.
LAST() - Fetches the last element in a collection of values.

Question 12

Scalar functions

Answer 12

LEN() - Calculates the total length of the given field (column).
UCASE() - Converts a collection of string values to uppercase characters.
LCASE() - Converts a collection of string values to lowercase characters.
MID() - Extracts substrings from a collection of string values in a table.
CONCAT() - Concatenates two or more strings.
RAND() - Generates a random collection of numbers of given length.
ROUND() - Calculates the round off integer value for a numeric field (or decimal point values).
NOW() - Returns the current data & time.
FORMAT() - Sets the format to display a collection of values

Question 13

Using the % wildcard to perform a simple search

Answer 13

SELECT *
FROM students
WHERE first_name LIKE ‘K%’

Question 14

Omitting the patterns using the NOT keyword

Answer 14

SELECT *
FROM students
WHERE first_name NOT LIKE ‘K%’

Question 15

Using the _ wildcard to match pattern at a specific position

Answer 15

SELECT *
FROM students
WHERE first_name LIKE ‘__K%’

Question 16

Matching patterns for specific length

Answer 16

SELECT * /* Matches first names with exactly four characters */
FROM students
WHERE first_name LIKE ‘____’