SQL Server: DBI202 - PT1

Q1: In a query that retrieves data from two tables that have been called joined together, the result returned includes only the records in the right table. Identify the type of join used in the above query?
Outer Join
Right Outer Join
Full Join
Right Inner join

Q2: How do you retrieve all columns from a table?
Select all
Select *
Select columns
Select all columns

Q3: Which keyword sorts data in descending order?
order
reverse
desc
asc

Q4: What will the following query return?
  SELECT COUNT(*) FROM Employees WHERE Age > 30;
All employees
Number of employees older than 30
Employees younger than 30
Average age

Q5: When you want to delete the Books table in the database, which of the following syntax is used?
DROP Books
DROP TABLE Books FROM library
DROP TABLE Books
DELETE TABLE Books
DELETE TABLE Books FROM library

Q6: What is the correct way to alias a column in SQL Server?
SELECT name = employee_name
SELECT name -> employee_name
SELECT name AS employee_name
SELECT name TO employee_name

Q7: What keyword is used to modify existing records?
ALTER
UPDATE
MODIFY
CHANGE

Q8: What is a PRIMARY KEY used for?
To allow duplicates
To reference another table
To increase performance
To uniquely identify a record

Q9: Which of the following statements is true about the COUNT function? (choose 3)
When using the COUNT function on a primary key field, it always returns correct results because the primary key field does not have null values.
When used in combination with the DISTINCT keyword, the COUNT function counts only distinct values.
The syntax of the COUNT function only works on fields with numeric data type.
The syntax of the COUNT function is COUNT(Expression)
Using the COUNT function on a foreign key field may return incorrect results because the foreign key field may have null values.

10: DML stands for
Data Moulding Language
Data Manipulation Language
Data Modeling Language
Data Marketing Language

Q11: What is a FOREIGN KEY?
A key from a different database
A random identifier
A key that references a primary key in another table
A key stored externally

Q12: Which constraint ensures all values in a column are unique?
NOT NULL
UNIQUE
KEY
DEFAULT

Q13: What is the default behavior of PRIMARY KEY regarding NULLs?
Does not allow NULLs
Allows multiple NULLs
Allows one NULL
Converts NULLs to 0

Q14: Which of the following statements is true about Join?
Join can be used in the FROM clause. In queries with a WHERE clause, Join cannot be used.
In SQL Server 2005 tables are joined together based on relationships between those tables.
Join shows the relationship between 2 tables
When used, Join eliminates the concept of foreign key relationships between tables.
Join can only be used on 2 tables

Q15: How do you define a column with a default value of 'USA'?
Country SET 'USA'
Country DEFAULT 'USA'
DEFAULT 'USA' ON Country
DEFAULT Country = 'USA'

Q16: Which of the following statements is false? (choose 2)
Outer joins will return all records of a certain table in the FROM clause, if those records satisfy the conditions in the WHERE and HAVING clauses.
There are 4 types of outer joins
Inner Hoins returns all records even if there is no corresponding record in the related table.
Inner hoins remove records that have no corresponding records in the related table.

Q17: Determine the correct syntax for LEFT OUTER JOIN
LEFT OUTER TABLE SELECT <Field_List> from <First_table><Second_table> ON
   First_Table.<common_Field>=Second_Table.<common_Field>
SELECT <Field_List> from <First_table> LEFT OUTER TABLE <Second_table> ON
   First_Table.<common_Field>=Second_Table.<common_Field>
SELECT <Field_List> from <First_table> LEFT OUTER JOIN <Second_table> ON
   First_Table.<common_Field>=Second_Table.<common_Field>
SELECT <Field_List> from <First_table> LEFT OUTER JOIN <Second_table> WHERE
   First_Table.<common_Field>=Second_Table.<common_Field>

Q18: What is the purpose of `GROUP BY`?
To join tables
To summarize data into groups
To delete duplicate rows
To restrict columns

Q19: What is the difference between `WHERE` and `HAVING`?
Both filter columns only
`WHERE` filters rows, `HAVING` filters groups
`HAVING` is used with INSERT
`WHERE` is used after GROUP BY

Q20: Which clause comes after `GROUP BY` in a valid query?
ORDER BY
HAVING
DISTINCT
WHERE

Q21: What does the COUNT(*) function do?
Returns the number of columns
Returns the number of rows
Returns total table size
Counts primary keys

Q22: Find the correct statement
  Statement 1 – Tables are created to store entities
  Statement 2 – Fields are created to represent attributes of the entity
Statement 2 is correct
Statement 1 is correct
Both statements are correct
None of the statements are correct

Q23: What will this query return?
  SELECT Department, COUNT(*) FROM Employees GROUP BY Department;
Total rows
Number of employees per department
Average salary
List of departments

Q24: Which clause is invalid in this order:
  SELECT ...  
  FROM ...  
  GROUP BY ...  
  WHERE ...
SELECT
WHERE
GROUP BY
FROM

Q25: How to return only departments with more than 5 employees?
WHERE COUNT(*) > 5
HAVING COUNT(*) > 5
GROUP COUNT(*) > 5
FILTER COUNT(*) > 5