Common Sql Queries Interview Questions - 3

Common SQL Queries Interview Questions :


1. Determine the name sex and age of the oldest student.
SELECT Name, Gender, (CURRENT_DATE-Dtnaiss)/365 AS Age
FROM Student
WHERE (CURRENT_DATE-Dtnaiss) /365 =
( SELECT MAX(( CURRENT_DATE-Dtnaiss) /365) FROM Student);


2. Display the marks of the student number 1 which are equal to the marks of the student number 2.
SELECT Note
FROM NOTES
WHERE Num=1 AND Note IN
(SELECT Note FROM NOTES WHERE Num=2);

3. Finding the names of everybody who works in the same department as a person called James

SELECT name FROM emp WHERE dept_no =
(SELECT dept_no FROM emp WHERE name = 'James')
or as a join statement, like this:-
SELECT e1.name FROM emp e1,emp e2
WHERE e1.dept_no = e2.dept_no AND e2name = 'James'

4. The SQL statement to find the departments that have employees with a salary higher than the average employee salary

 SELECT name FROM dept

WHERE id IN
     (
      SELECT dept_id FROM emp
      WHERE sal >
          (SELECT avg(sal)FROM emp)
     )

5. Write the SQL to use a sub query which will not return any rows - when just the table structure is required and not any of the data.

CREATE TABLE new_table AS

SELECT * from table_orig WHERE 1=0;
The sub query returns no data but does return the column names and data types to the 'create table' statement.

6. How do you find the Second highest Salary?

SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE)



7. Finding duplicates in a table

SELECT name, COUNT (name) AS NumOccurrences FROM users 

GROUP BY email HAVING (COUNT (name) > 1)

You could also use this technique to find rows that occur exactly once:

SELECT name FROM users GROUP BY name HAVING (COUNT (name) = 1) 

8.While deleting a row from the database,i need to check based upon the Name of the Employee and The Employee Number. How to give 2 Conditions in an SQL Query ?

 "DELETE FROM Employees WHERE Emp_Name='" & Txt_Name.Text.Trim & "' 

AND Emp_Number= '" & Txt_Empno.Text.Trim & "'"
 

 9. Common SQL Syntax used in database interaction

9a.   Select Statement
SELECT "column_name" FROM "table_name"

9b.    Distinct
SELECT DISTINCT "column_name" FROM "table_name"

9c.    Where
SELECT "column_name" FROM "table_name" WHERE "condition"

9d.   And/Or
SELECT "column_name" FROM "table_name" WHERE "simple condition" {[AND|OR] "simple condition"}+

9e.   In
SELECT "column_name" FROM "table_name" WHERE "column_name" IN ('value1', 'value2', ...)

9f.    Between
SELECT "column_name" FROM "table_name" WHERE "column_name" BETWEEN 'value1' AND 'value2'

9g.   Like
SELECT "column_name" FROM "table_name" WHERE "column_name" LIKE {PATTERN}

9h.   Order By
SELECT "column_name" FROM "table_name" [WHERE "condition"] ORDER BY "column_name" [ASC, DESC]

9i.    Count
SELECT COUNT("column_name") FROM "table_name"

9j.    Group By
SELECT "column_name1", SUM("column_name2") FROM "table_name" GROUP BY "column_name1"

9k.   Having
SELECT "column_name1", SUM("column_name2") FROM "table_name" GROUP BY "column_name1" HAVING (arithematic function condition)

9l.   Create Table Statement
CREATE TABLE "table_name" ("column 1" "data_type_for_column_1","column 2" "data_type_for_column_2",…)

9m.  Drop Table Statement
DROP TABLE "table_name"

9n.   Truncate Table Statement
TRUNCATE TABLE "table_name"

9m.  Insert Into Statement
INSERT INTO "table_name" ("column1", "column2", ...) VALUES ("value1", "value2", ...)

9o.   Update Statement
UPDATE "table_name" SET "column_1" = [new value] WHERE {condition}

9p.   Delete From Statement
DELETE FROM "table_name" WHERE {condition}

Browse for more Database Testing Related Posts 


Common Sql Queries Interview Questions 1



Common Sql Queries Interview Questions 2



Common Sql Queries Interview Questions 3

Common Sql Queries Interview Questions 4

For more articles on Manual Testing Log On to Manual Testing Articles