SQL Fundamentals
What is the process of requesting data from a database and receiving back the results called?
A database query
Which SQL query correctly calculates the total sales for each salesperson and filters the results to only show salespeople with total sales greater than 10,000? A) SELECT salesperson_id, SUM(sales_amount) FROM sales GROUP BY salesperson_id HAVING SUM(sales_amount) > 10000; B) SELECT salesperson_id, SUM(sales_amount) FROM sales WHERE SUM(sales_amount) > 10000 GROUP BY salesperson_id; C) SELECT salesperson_id, SUM(sales_amount) FROM sales HAVING SUM(sales_amount) > 10000 GROUP BY salesperson_id;
A is correct because it uses the GROUP BY clause to group the sales by salesperson_id and then filters the groups with the HAVING clause to only include those with total sales greater than 10,000. B is incorrect because the WHERE clause cannot be used with aggregate functions like SUM(); it is applied before grouping. C is incorrect because the HAVING clause must come after the GROUP BY clause, and in C, it's placed before the GROUP BY.
Which of the following queries correctly uses the HAVING clause to filter groups based on an aggregate function? A) SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 50000 GROUP BY department_id; B) SELECT department_id, SUM(salary) AS total_salary FROM employees GROUP BY department_id HAVING total_salary > 50000; C) SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 10;
A is incorrect because the WHERE clause cannot use aggregate functions like AVG(). B is incorrect because the HAVING clause is referring to total_salary, which is an alias that cannot be used directly in the HAVING clause. The correct query would use HAVING SUM(salary) > 50000. WOULD WORK IN SSMS C is correct because it uses the HAVING clause correctly with the COUNT(*) aggregate function.
When should you use subqueries vs joins?
A subquery is often used when you need to use the result of a query as a filter for another query. It can sometimes be less efficient than using a JOIN, especially for large datasets. Joins are typically preferred when you need to combine data from multiple tables based on related columns, as they can be more efficient in certain scenarios.
Which of the following SQL queries correctly retrieves a list of customer names and their corresponding order IDs from the customers and orders tables, where the customer_id is the foreign key in the orders table referencing the customer_id primary key in the customers table? A) SELECT customers.first_name, customers.last_name, orders.order_id FROM customers INNER JOIN orders ON orders.customer_id = customers.customer_id; B) SELECT customers.first_name, customers.last_name, orders.order_id FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id; C) SELECT customers.first_name, customers.last_name, orders.order_id FROM customers RIGHT JOIN orders ON customers.customer_id = orders.customer_id;
A) SELECT customers.first_name, customers.last_name, orders.order_id FROM customers INNER JOIN orders ON orders.customer_id = customers.customer_id;
Which of the following best describes a constraint in SQL? A) A constraint is a rule applied to the data stored in a database to enforce data integrity, and it can restrict the type of data that can be inserted into a table. B) A constraint is a type of index that helps speed up data retrieval by organizing data into a predefined order. C) A constraint is a stored procedure that automatically performs actions like data validation or modification upon data changes.
A) A constraint is a rule applied to the data stored in a database to enforce data integrity, and it can restrict the type of data that can be inserted into a table.
What is a primary key? A) A key that ensures data uniqueness in a column and cannot be NULL B) A key used to link two tables together C) A key that encrypts sensitive data in the database D) A key used to sort records in a table
A) A key that ensures data uniqueness in a column and cannot be NULL
Which of the following best describes a materialized view in SQL? A) A materialized view is a virtual table that stores the result of a query in a persistent format, and it is automatically updated when the underlying data changes. B) A materialized view is a virtual table that stores the result of a query, but it does not persist the data and is updated manually by the user. C) A materialized view is a stored procedure that allows users to retrieve data from multiple tables simultaneously without storing the result.
A) A materialized view is a virtual table that stores the result of a query in a persistent format, and it is automatically updated when the underlying data changes.
Which of the following best describes the primary index in SQL? A) A primary index is automatically created on the primary key column to speed up query processing and enforce uniqueness in a table. B) A primary index is used to define relationships between tables and does not impact query performance. C) A primary index is manually created to sort data in a specific order without enforcing uniqueness.
A) A primary index is automatically created on the primary key column to speed up query processing and enforce uniqueness in a table.
Which of the following best describes a scalar subquery in SQL Server? A) A scalar subquery is a subquery that returns a single row and a single column, and its result is treated as a single value in the outer query. B) A scalar subquery is a subquery that can return multiple rows and columns, but only the first row of the result is used in the outer query. C) A scalar subquery is a subquery that is used only in the SELECT clause and cannot be used in WHERE, HAVING, or FROM.
A) A scalar subquery is a subquery that returns a single row and a single column, and its result is treated as a single value in the outer query.
Which of the following best describes the unique index in SQL? A) A unique index is used to enforce uniqueness of values in one or more columns while improving query performance by indexing those columns. B) A unique index is created to define relationships between tables, ensuring data integrity by linking tables together. C) A unique index automatically creates a primary key constraint and ensures that no NULL values are allowed in indexed columns.
A) A unique index is used to enforce uniqueness of values in one or more columns while improving query performance by indexing those columns.
Which of the following statements best describes a unique key in SQL? A) A unique key ensures that all values in a column are distinct, but it allows for one NULL value in the column. B) A unique key ensures that all values in a column are distinct and allows multiple NULL values in the column. C) A unique key is similar to a primary key, but it allows duplicate values in the column.
A) A unique key ensures that all values in a column are distinct, but it allows for one NULL value in the column.
What is the definition of a view in SQL? A) A view is a stored query that presents data from one or more tables without storing it physically - a virtual table. Views only allow you to view or read data (SELECT operation). B) A view is a temporary table that can be modified in place. C) A view is a table that holds calculated data from other tables.
A) A view is a stored query that presents data from one or more tables without storing it physically - a virtual table. Views only allow you to view or read data (SELECT operation.
Which of the following SQL statements correctly creates an employees table with the following requirements: employee_id is the primary key. first_name and last_name cannot be NULL. email must be unique. A) CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE ); B) CREATE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE ); C) CREATE TABLE employees ( employee_id INT NOT NULL PRIMARY KEY, first_name VARCHAR(100), last_name VARCHAR(100), email VARCHAR(100) UNIQUE NOT NULL );
A) CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE );
Which of the following SQL statements correctly creates an orders table with the following requirements: order_id is the primary key. customer_id is a foreign key that references the id column in the customers table. A) CREATE TABLE orders ( order_id INT PRIMARY KEY, order_date DATE, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(id) ); B) CREATE TABLE orders ( order_id INT PRIMARY KEY, order_date DATE, customer_id INT, CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ); C) CREATE TABLE orders ( order_id INT PRIMARY KEY, order_date DATE, customer_id INT, PRIMARY KEY (customer_id), FOREIGN KEY (customer_id) REFERENCES customers(id) );
A) CREATE TABLE orders ( order_id INT PRIMARY KEY, order_date DATE, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(id) ); B is also correct but uses a named constraint (fk_customer) for the foreign key, which is valid but less common than directly using the FOREIGN KEY syntax. Newly created table called orders has a column called customer_id and this column corresponds with the column id in the customers table
Which of the following SQL statements correctly drops a table named students? A) DROP TABLE students; B) DELETE TABLE students; C) REMOVE TABLE students;
A) DROP TABLE students;
Which of the following SQL statements correctly inserts a new record into the students table, where the table has columns id, first_name, and last_name? A) INSERT INTO students VALUES ('1', 'John', 'Doe'); B) INSERT INTO students (id, first_name, last_name) VALUES (1, 'John', 'Doe'); C) INSERT INTO students (first_name, last_name) VALUES ('John', 'Doe'); D) INSERT INTO students (id, first_name) VALUES (1, 'John');
A) INSERT INTO students VALUES ('1', 'John', 'Doe'); * B) INSERT INTO students (id, first_name, last_name) VALUES (1, 'John', 'Doe'); B is the correct syntax because it explicitly specifies both the column names and the corresponding values, ensuring the correct order and matching data types. A could work, but it doesn't specify the column names, so it relies on the order of columns in the table, which can be risky if the table structure changes. In SQL, string values should be enclosed in single quotes ('), not without quotes or with double quotes.
What does the OFFSET-FETCH clause do in SQL Server (SSMS)? A) It limits the number of rows returned and specifies which rows to skip, useful for pagination in queries. B) It calculates the total number of rows in a query and adds the result to the output. C) It groups rows based on a specified column and calculates aggregate values for each group.
A) It limits the number of rows returned and specifies which rows to skip, useful for pagination in queries.
Which of the following SQL statements correctly retrieves all columns from the "customers" table, sorted by the "last_name" column in ascending order? A) SELECT * FROM customers ORDER BY last_name ASC; B) SELECT * FROM customers WHERE ORDER BY last_name; C) SELECT * FROM customers ORDER last_name ASC; D) SELECT * FROM customers SORT BY last_name;
A) SELECT * FROM customers ORDER BY last_name ASC;
Which of the following SQL queries correctly sorts the result set of employees by their salary in descending order, followed by sorting their names in ascending order? A) SELECT * FROM employees ORDER BY salary DESC, name ASC; B) SELECT * FROM employees ORDER BY name ASC, salary DESC; C) SELECT * FROM employees ORDER BY salary ASC, name DESC;
A) SELECT * FROM employees ORDER BY salary DESC, name ASC;
Which of the following SQL queries correctly uses the WHERE clause to select all employees who have a salary greater than 50000? A) SELECT * FROM employees WHERE salary > 50000; B) SELECT * FROM employees WHERE salary = 50000; C) SELECT * FROM employees WHERE salary < 50000;
A) SELECT * FROM employees WHERE salary > 50000;
Which of the following SQL queries finds records in Table1 that are not present in Table2? Table 1 and Table 2 have 2 columns - ID and Name. A) SELECT ID, Name FROM Table1 EXCEPT SELECT ID, Name FROM Table2; B) SELECT ID, Name FROM Table1 EXCEPT ALL SELECT ID, Name FROM Table2; C) SELECT ID, Name FROM Table2 EXCEPT SELECT ID, Name FROM Table1;
A) SELECT ID, Name FROM Table1 EXCEPT SELECT ID, Name FROM Table2; Option C reverses the tables and performs the opposite operation, finding records in Table2 that are not in Table1, which isn't what the question asks for.
Which of the following SQL queries correctly retrieves the top 5 highest-paid employees from the "employees" table, ordered by "salary" in descending order? A) SELECT TOP 5 first_name, last_name, salary FROM employees ORDER BY salary DESC; B) SELECT first_name, last_name, salary FROM employees LIMIT 5 ORDER BY salary DESC; C) SELECT TOP 5 first_name, last_name, salary FROM employees FETCH NEXT 5 ROWS ONLY;
A) SELECT TOP 5 first_name, last_name, salary FROM employees ORDER BY salary DESC;
Which SQL query correctly implements pagination by skipping the first 10 rows and fetching the next 5 rows from the "products" table, ordered by "product_name" in ascending order (in SQL Server)? A) SELECT product_name, price FROM products ORDER BY product_name ASC OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY; B) SELECT product_name, price FROM products ORDER BY product_name ASC LIMIT 5 OFFSET 10; C) SELECT product_name, price FROM products ORDER BY product_name ASC FETCH NEXT 10 ROWS ONLY OFFSET 5;
A) SELECT product_name, price FROM products ORDER BY product_name ASC OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
What does the OFFSET clause in SQL do? A) Skips a specified number of rows before returning the remaining rows from a query. B) Limits the maximum number of rows returned by a query. C) Filters rows based on specific conditions.
A) Skips a specified number of rows before returning the remaining rows from a query.
What does the LIMIT (TOP in SSMS) clause in SQL do? A) Specifies the maximum number of rows to return from a query. B) Specifies a condition to filter rows before grouping data. C) Specifies the starting point for retrieving rows in a query.
A) Specifies the maximum number of rows to return from a query.
Which of the following correctly describes the EXCEPT operator in SQL? A) The EXCEPT operator returns all rows from the first query, excluding rows that also appear in the second query, and removes duplicates. B) The EXCEPT operator combines the results of two queries, including duplicates from both, and sorts the data automatically. C) The EXCEPT operator returns all rows from both queries, excluding those that appear in the second query, and includes duplicates.
A) The EXCEPT operator returns all rows from the first query, excluding rows that also appear in the second query, and removes duplicates. Returns unique records from first query
What is the purpose of the GROUP BY clause in SQL? A)The GROUP BY clause is used to combine rows that have the same values into summary rows, such as finding the total or average of grouped data. B)The GROUP BY clause is used to sort the result set by one or more columns in ascending or descending order. C)The GROUP BY clause is used to filter rows based on a specified condition, similar to the WHERE clause.
A) The GROUP BY clause is used to combine rows that have the same values into summary rows, such as finding the total or average of grouped data.
Which of the following statements is true about the UNION operator in SQL? A) The UNION operator combines the results of two or more SELECT queries and automatically removes duplicate rows from the final result set. B) The UNION operator combines the results of two or more SELECT queries but includes duplicate rows in the final result set. C) The UNION operator can only be used when the columns being selected have different data types. D) The UNION operator is used to combine rows from different tables into a single table permanently.
A) The UNION operator combines the results of two or more SELECT queries and automatically removes duplicate rows from the final result set.
Which of the following SQL statements correctly updates the age of the student with id 3 in the students table to 20? A) UPDATE students SET age = 20 WHERE id = 3; B) UPDATE students SET age = 20; C) UPDATE students SET age = 20 WHERE first_name = 'John'; D) UPDATE students SET id = 3, age = 20;
A) UPDATE students SET age = 20 WHERE id = 3;
Which of the following best describes the purpose of the WHERE SQL clause? A)The WHERE clause is used to limit the number of rows returned by a query, specifying which rows are to be included in the result set. B)The WHERE clause is used to define the structure of a table, including its columns and data types. C)The WHERE clause is used to sort the result set based on specified columns in ascending or descending order.
A)The WHERE clause is used to limit the number of rows returned by a query, specifying which rows are to be included in the result set.
Explain the general synax of the ALTER statement:
ALTER TABLE table_name { ADD column_name column_definition | DROP COLUMN column_name | MODIFY column_definition | RENAME TO new_table_name }; ADD column_name column_definition : The ADD keyword is used to add one or more new columns to an existing table. DROP COLUMN column_name : The DROP COLUMN keyword is used to remove an existing column from a table. MODIFY column_definition : The MODIFY or ALTER COLUMN keyword is used to change the definition of an existing column (like changing its data type or size). RENAME TO new_table_name : The RENAME TO keyword allows you to change the name of a table The ALTER statement is primarily used for modifying the schema of a table (e.g., changing column names, data types, or adding constraints), not for modifying the data itself. In SSMS, each operation would require it's own alter statement.
Which of the following statements correctly matches the SQL data types to their appropriate definitions? A) INT: Stores integer values (whole numbers). VARCHAR: Stores fixed-length character strings. CHAR: Stores variable-length character strings. DATE: Stores floating-point numbers with decimal points. DECIMAL/NUMERIC: Stores exact numeric values with fixed precision and scale. FLOAT: Stores dates and times in 'YYYY-MM-DD' format. B) INT: Stores integer values (whole numbers). VARCHAR: Stores variable-length character strings. CHAR: Stores fixed-length character strings. DATE: Stores date and time values in 'YYYY-MM-DD' format. DECIMAL/NUMERIC: Stores exact numeric values with fixed precision and scale. FLOAT: Stores approximate numeric values with floating points.
B) INT: Stores integer values (whole numbers). VARCHAR: Stores variable-length character strings. CHAR: Stores fixed-length character strings. DATE: Stores date and time values in 'YYYY-MM-DD' format. DECIMAL/NUMERIC: Stores exact numeric values with fixed precision and scale. FLOAT: Stores approximate numeric values with floating points. DECIMAL(n, m) / NUMERIC(n, m) Stores exact numeric values with fixed precision (n represents total digits and m represents the digits after the decimal point).
You have two tables, Employees1 and Employees2. Each table has the following columns: EmployeeID, Name, Department. You want to create a result set that combines all employees from both tables, ensuring that there are no duplicate rows. Which SQL query should you use? A) SELECT EmployeeID, Name, Department FROM Employees1 UNION ALL SELECT EmployeeID, Name, Department FROM Employees2; B) SELECT EmployeeID, Name, Department FROM Employees1 UNION SELECT EmployeeID, Name, Department FROM Employees2; C) SELECT EmployeeID, Name, Department FROM Employees1 INTERSECT SELECT EmployeeID, Name, Department FROM Employees2;
B) SELECT EmployeeID, Name, Department FROM Employees1 UNION SELECT EmployeeID, Name, Department FROM Employees2;
Which of the following SQL queries finds common records between two tables, Products1 and Products2? Both tables have the following columns: ProductID, ProductName, Price. A) SELECT ProductID, ProductName, Price FROM Products1 UNION SELECT ProductID, ProductName , Price FROM Products2; B) SELECT ProductID, ProductName, Price FROM Products1 INTERSECT SELECT ProductID, ProductName, Price FROM Products2; C) SELECT ProductID, ProductName, Price FROM Products1 UNION ALL SELECT ProductID, ProductName, Price FROM Products2;
B) SELECT ProductID, ProductName, Price FROM Products1 INTERSECT SELECT ProductID, ProductName, Price FROM Products2;
You have two tables, Products1 and Products2. Each table has columns ProductID, ProductName, Price. Which SQL query correctly combines the rows from both tables, including all duplicates? A) SELECT ProductID, ProductName, Price FROM Products1 UNION ALL SELECT ProductID, ProductName FROM Products2; B) SELECT ProductID, ProductName, Price FROM Products1 UNION ALL SELECT ProductID, ProductName, Price FROM Products2; C) SELECT ProductID, ProductName FROM Products1 UNION ALL SELECT ProductID, ProductName, Price FROM Products2;
B) SELECT ProductID, ProductName, Price FROM Products1 UNION ALL SELECT ProductID, ProductName, Price FROM Products2;
Which of the following best describes a composite key in SQL? A) A composite key is a key that consists of a combination of columns, where each column can have null values. B) A composite key is a key that consists of two or more columns, where the combination of values in those columns must be unique across all rows in the table. C) A composite key is a single column that is used as the primary identifier for rows in a table.
B) A composite key is a key that consists of two or more columns, where the combination of values in those columns must be unique across all rows in the table. In SQL Server, NULL values can appear in individual columns of a composite key. However, the combination of values in all the columns that form the composite key must still be unique.
Which of the following best defines a Correlated Subquery in SQL Server? A) A correlated subquery is independent of the outer query and can be executed on its own. B) A correlated subquery is a type of subquery that references columns from the outer query, meaning it is evaluated for each row processed by the outer query. C) A correlated subquery is a subquery that always returns only one value.
B) A correlated subquery is a type of subquery that references columns from the outer query, meaning it is evaluated for each row processed by the outer query.
What is a relational database? A) A database that stores data in a single table B) A database that stores data in rows and columns and uses relationships between tables C) A database used to manage files and documents D) A database that stores unstructured data in a flat format
B) A database that stores data in rows and columns and uses relationships between tables
Which of the following best describes a function in SQL? A) A function is a stored procedure that performs an action, modifies data, and does not return a value. B) A function is a predefined query that returns a value and can be used in SQL expressions, often used to encapsulate business logic. C) A function is a trigger that automatically executes when specific changes occur in the databa
B) A function is a predefined query that returns a value and can be used in SQL expressions, often used to encapsulate business logic.
What is a foreign key? A) A key that defines the structure of a database table B) A key that is used to create relationships between tables by referencing the primary key of another table C) A key that stores encrypted values D) A key that ensures a table's columns are unique
B) A key that is used to create relationships between tables by referencing the primary key of another table
What is a "server" in the context of SQL? A) A network device that stores and manages database backups B) A software application that executes SQL queries and manages database connections C) A physical machine that runs web applications D) A file storage system used for data retrieval
B) A software application that executes SQL queries and manages database connections
Which of the following best describes a subquery in SQL Server? A) A subquery is a query that is executed after the main query and its result is used to filter rows from the main query. B) A subquery is a query embedded within another query that can return a single value, a set of values, or a table. C) A subquery is a query that can only be used in the SELECT clause of a SQL query.
B) A subquery is a query embedded within another query that can return a single value, a set of values, or a table.
What is the definition of a table in SQL? A) A table is a stored collection of SQL queries. B) A table is a structured set of data organized in rows and columns used to store information - fundamental component of relational databases. C) A table is a type of database used to hold multiple databases.
B) A table is a structured set of data organized in rows and columns used to store information - fundamental component of relational databases.
Which of the following best describes a trigger in SQL? A) A trigger is a stored procedure that performs an action when manually invoked by the user, but it does not automatically execute based on data changes. B) A trigger is a stored procedure in SQL that executes automatically in response to specific events like INSERT, UPDATE, or DELETE on a table or view. C) A trigger is a type of index used to speed up query performance by optimizing data retrieval in SQL.
B) A trigger is a stored procedure in SQL that executes automatically in response to specific events like INSERT, UPDATE, or DELETE on a table or view.
Given the following requirements for a table named employees: Add a new column salary of type DECIMAL(10, 2). Delete the column phone_number. Modify the column email to increase its size to VARCHAR(500). Rename the table to staff_members. A) ALTER TABLE employees ADD salary DECIMAL(10, 2); ALTER TABLE employees DROP COLUMN phone_number; ALTER TABLE employees ALTER COLUMN email VARCHAR(500); ALTER TABLE employees RENAME TO staff_members; B) ALTER TABLE employees ADD salary DECIMAL(10, 2); ALTER TABLE employees DROP COLUMN phone_number; ALTER TABLE employees ALTER COLUMN email VARCHAR(500); EXEC sp_rename 'employees', 'staff_members';
B) ALTER TABLE employees ADD salary DECIMAL(10, 2); ALTER TABLE employees DROP COLUMN phone_number; ALTER TABLE employees MODIFY email VARCHAR(500); EXEC sp_rename 'employees', 'staff_members'; B is correct because it performs each operation separately and uses sp_rename to rename the table, which is the correct way in SQL Server.
Which of the following statements best describes an index in SQL? A) An index is used to enforce uniqueness and ensure data integrity within a table. B) An index is used to speed up query processing by creating a separate data structure that allows faster search and retrieval. C) An index is used to define the relationship between two tables.
B) An index is used to speed up query processing by creating a separate data structure that allows faster search and retrieval.
Which of the following SQL statements correctly creates a students table with columns for id, first_name, last_name, and age, where id is the primary key? A) CREATE TABLE students ( id INT, first_name VARCHAR(50), last_name VARCHAR(50), age INT, PRIMARY KEY(id) ); B) CREATE TABLE students ( id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), age INT ); C) CREATE students ( id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), age INT ); D) CREATE TABLE students ( id INT NOT NULL, first_name VARCHAR(50), last_name VARCHAR(50), age INT, PRIMARY KEY(id) );
B) CREATE TABLE students ( id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), age INT ); A is also correct but slightly less concise. The PRIMARY KEY(id) is correctly placed at the end of the column definitions, but it's still valid.
Which of the following SQL statements will correctly remove all records from the students table? A) DELETE FROM students WHERE id = 1; B) DELETE FROM students; C) DELETE students WHERE id = 1; D) DELETE * FROM students;
B) DELETE FROM students; Since where condition is not specified, we delete all records/rows from the table.
What is the purpose of the ALTER SQL statement? A) It deletes an existing table and all its data. B) It modifies the structure of an existing database object. You can use it to add, modify, or delete columns, change constraints, or rename the table itself. C) It retrieves data from one or more tables in the database.
B) It modifies the structure of an existing database object. You can use it to add, modify, or delete columns, change constraints, or rename the table itself.
What is the purpose of the DROP SQL statement? A) It deletes all rows from a table without removing the table structure. B) It removes a table, view, or other database object along with all its data from the database permanently. C) It removes all records from a table while keeping the table structure intact.
B) It removes a table, view, or other database object along with all its data from the database permanently. A describes DELETE statement, C described TRUNCATE statement
Which of the following best describes the purpose of normalization in SQL? A) Normalization is the process of creating multiple tables to store redundant data for faster querying and reporting. B) Normalization is the process of organizing database structures to reduce redundancy and improve data integrity. C) Normalization is the process of creating indexes on frequently used columns to speed up query performance.
B) Normalization is the process of organizing database structures to reduce redundancy and improve data integrity.
Which of the following statements correctly describes the INTERSECT operator in SQL? A) The INTERSECT operator combines the result sets of two SELECT queries, including all rows from both queries, and removes duplicates. B) The INTERSECT operator returns only the rows that appear in both result sets, excluding duplicates. C) The INTERSECT operator combines the result sets of two SELECT queries, including all rows, even duplicates, and sorts the result automatically.
B) The INTERSECT operator returns only the rows that appear in both result sets, excluding duplicates. The INTERSECT operator is useful when you want to find the common rows between two queries, and it ensures that only unique, matching rows are returned.
Which of the following statements is true about the UNION ALL operator in SQL? A) The UNION ALL operator combines the results of two or more SELECT queries and removes duplicate rows from the final result set. B) The UNION ALL operator combines the results of two or more SELECT queries and includes all rows, including duplicates, in the final result set. C) The UNION ALL operator can only be used to combine the results of queries that return the same data type. D) The UNION ALL operator can only be used with two SELECT queries and not more.
B) The UNION ALL operator combines the results of two or more SELECT queries and includes all rows, including duplicates, in the final result set.
What is the purpose of the HAVING clause in SQL? A) To filter rows based on conditions before grouping occurs. B) To filter groups of rows after an aggregate function has been applied. C) To specify the columns to sort the query results in ascending or descending order.
B) To filter groups of rows after an aggregate function has been applied.
What is the purpose of the SQL SELECT statement? A) To delete data from a database B) To retrieve data columns from one or more tables in a database C) To modify the structure of a table D) To update existing data in a table
B) To retrieve data columns from one or more tables in a database
What is the purpose of an alias in SQL? A)To define a new database table for storing data. B)To temporarily rename a table or column for the duration of a query to make it easier to refer to. C)To automatically generate the values in a column during query execution.
B) To temporarily rename a table or column for the duration of a query to make it easier to refer to.
What is the purpose of a SQL JOIN? A)To combine multiple rows of data from one table into a single row based on a condition. B)To retrieve data from two or more tables based on a related column between them. C)To delete rows from one table based on a condition from another table.
B)To retrieve data from two or more tables based on a related column between them. The related column used in a JOIN is often referred to as a foreign key in one table, which typically corresponds to the primary key in another table.
What are similarities and differences between views and stored procedures?
Both are precompiled set of SQL statements. Views allow you to "read" specific data from a table, whereas Stored Procedures allow you to perform other operations besides viewing data from a table.
Which of the following SQL queries will return all employees, including those without a department, as well as all departments, including those without employees? A) SELECT employees.first_name, employees.last_name, departments.department_name FROM employees LEFT OUTER JOIN departments ON employees.employee_id = departments.employee_id; B) SELECT employees.first_name, employees.last_name, departments.department_name FROM employees RIGHT OUTER JOIN departments ON employees.employee_id = departments.employee_id; C) SELECT employees.first_name, employees.last_name, departments.department_name FROM employees FULL OUTER JOIN departments ON employees.employee_id = departments.employee_id;
C) SELECT employees.first_name, employees.last_name, departments.department_name FROM employees FULL OUTER JOIN departments ON employees.employee_id = departments.employee_id; C) uses a FULL OUTER JOIN, which returns all employees and all departments, including those without corresponding matches. Non-matching rows will have NULL values in the respective columns.
What is the definition of a stored procedure in SQL? A) A stored procedure is a SQL query that can be executed manually. B) A stored procedure is a type of table used for complex calculations. C) A stored procedure is a precompiled set of SQL statements that are executed to perform multiple types of operations, such as SELECT, INSERT, UPDATE, DELETE, or other complex business logic.
C) A stored procedure is a precompiled set of SQL statements that are executed to perform multiple types of operations, such as SELECT, INSERT, UPDATE, DELETE, or other complex business logic.
Which SQL query will return all customers along with any orders they have placed, but only those customers who have placed orders? A) SELECT customers.customer_name, orders.order_id FROM customers LEFT OUTER JOIN orders ON customers.customer_id = orders.customer_id; B) SELECT customers.customer_name, orders.order_id FROM customers RIGHT OUTER JOIN orders ON customers.customer_id = orders.customer_id; C) SELECT customers.customer_name, orders.order_id FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id;
C) SELECT customers.customer_name, orders.order_id FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id; C) uses an INNER JOIN, which will return only customers who have placed orders, and it will exclude those customers who haven't placed any orders. This is the correct choice.
Which SQL query will return all products along with any corresponding orders, including products that have no orders and orders that do not correspond to any product? A) SELECT products.product_name, orders.order_id FROM products LEFT OUTER JOIN orders ON products.product_id = orders.product_id; B) SELECT products.product_name, orders.order_id FROM products RIGHT OUTER JOIN orders ON products.product_id = orders.product_id; C) SELECT products.product_name, orders.order_id FROM products FULL OUTER JOIN orders ON products.product_id = orders.product_id;
C) SELECT products.product_name, orders.order_id FROM products FULL OUTER JOIN orders ON products.product_id = orders.product_id; C) uses a FULL OUTER JOIN, which will return all products and all orders, including those without matches. If a product has no orders, the order_id column will be NULL, and if an order has no corresponding product, the product_name column will be NULL.
What is the primary purpose of the SQL INSERT statement? A) To retrieve data from a table B) To update existing data in a table C) To add new rows of data into a table D) To delete data from a table
C) To add new rows of data into a table
What is the primary purpose of the SQL CREATE statement? A) To modify existing records in a table B) To retrieve data from a table C) To define a new table or database object D) To remove existing records from a table
C) To define a new table or database object
What is the primary purpose of the SQL UPDATE statement? A) To delete existing records from a table B) To retrieve data from a table C) To modify existing data in a table D) To add new rows to a table
C) To modify existing data in a table
What is the purpose of the ORDER BY clause in SQL? A)The ORDER BY clause is used to filter rows based on a specified condition. B)The ORDER BY clause is used to group rows with the same values into summary rows, like "total" or "average." C)The ORDER BY clause is used to sort the result set by one or more columns in ascending or descending order.
C)The ORDER BY clause is used to sort the result set by one or more columns in ascending or descending order.
Explain how you create an INDEX in SQL:
CREATE INDEX index_name ON table_name (column1, column2, ...);
Show an example of all constraints used:
CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, -- PRIMARY KEY constraint Name VARCHAR(100) NOT NULL, -- NOT NULL constraint Email VARCHAR(100) UNIQUE, -- UNIQUE constraint Age INT CHECK (Age >= 18), -- CHECK constraint DepartmentID INT, FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) -- FOREIGN KEY constraint );
Explain the syntax of the CREATE statement:
CREATE TABLE table_name ( column1 datatype constraint, column2 datatype constraint, column3 datatype constraint, ... ); CREATE TABLE: This part of the statement specifies that you want to create a new table. table_name: The name of the table you are creating. column1, column2, column3, ...: These are the names of the columns you want to define in the table. datatype: Defines the type of data that can be stored in the column (e.g., INT, VARCHAR, DATE, etc.). constraint (optional): Constraints such as PRIMARY KEY, NOT NULL, UNIQUE, etc., can be added to enforce rules on the data in each column.
What is a schema in the context of a database? A) The physical location of data files in the database B) A backup of the database C) The set of user permissions within the database D) A template or structure that defines how data is organized and stored in the database
D) A template or structure that defines how data is organized and stored in the database
What is the primary purpose of the SQL DELETE statement? A) To add new records to a table B) To retrieve data from a table C) To modify existing data in a table D) To remove existing records from a table
D) To remove existing records from a table
Explain syntax of the DELETE statement:
DELETE FROM table_name WHERE condition; DELETE FROM table_name : Specifies table you want to delete data from WHERE condition : A condition to filter the rows that will be deleted. Only the rows that match the condition will be removed. If omitted, all rows in the table will be deleted, so it's very important to use the WHERE clause unless you intend to delete everything.
Explain syntax of the DROP statement:
DROP TABLE table_name; table_name represents the table you want to delete from the database permanently. DROP is a destructive operation: Once you drop an object, it cannot be recovered unless there is a backup. You need appropriate permissions to drop a database object (e.g., administrative privileges).
What are some common examples of constraints?
Examples of constraints include PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and NOT NULL.
Match the SQL JOIN types with their definitions: 1) INNER JOIN 2) OUTER LEFT JOIN 3) OUTER RIGHT JOIN 4) FULL OUTER JOIN A) Combines rows from both tables, but only includes rows where there is a match in both tables. B) Combines all rows from the left table with the matching rows from the right table. If there is no match, NULL values are returned for columns from the right table. C) Combines all rows from the right table with the matching rows from the left table. If there is no match, NULL values are returned for columns from the left table. D) Combines all rows from both tables, including unmatched rows from both sides, and fills with NULLs for missing matches.
INNER JOIN - Combines rows from both tables, but only includes rows where there is a match in both tables. OUTER LEFT JOIN - Combines all rows from the left table with the matching rows from the right table. If there is no match, NULL values are returned for columns from the right table. OUTER RIGHT JOIN - Combines all rows from the right table with the matching rows from the left table. If there is no match, NULL values are returned for columns from the left table. FULL OUTER JOIN - Combines all rows from both tables, including unmatched rows from both sides, and fills with NULLs for missing matches. *When you use JOIN keyword, SQL defaults to an inner join. In SQL, it is generally considered good practice to explicitly specify the type of join you are using.
Explain syntax of SQL INSERT statement.
INSERT INTO table_name (column1, column2, column3, ...); VALUES (value1, value2, value3, ...); INSERT INTO: This clause tells SQL that you're inserting data into a table. table_name: The name of the table where you want to insert the data. (column1, column2, column3, ...): A list of column names in the table where data is going to be inserted. This list is optional, but it's good practice to specify which columns you're inserting data into, especially if the table has multiple columns. VALUES (value1, value2, value3, ...): A list of the corresponding values that will be inserted into the columns specified earlier. The values must appear in the same order as the columns. When inserting records into a table using the SQL INSERT statement, you must provide a value for every NOT NULL column. You can only omit a column from the INSERT statement if the column allows NULL values.
What does * mean in SQL?
In SQL, the asterisk (*) is a wildcard character used in the SELECT statement to represent all columns of a table. When you use *, it tells the SQL engine to select and return all available columns for each row in the specified table(s).
Explain how SQL language, SQL Server Management Studio, and Microsoft SQL Server interact? What roles do they play?
Microsoft SQL Server is the DBMS (Database Management System), which is the software responsible for storing, managing, and retrieving your data in a database. SQL Server Management Studio (SSMS) is a tool or interface that allows you to interact with Microsoft SQL Server You write SQL queries in SSMS. SSMS sends those queries to Microsoft SQL Server (the DBMS). The DBMS (SQL Server) processes the SQL queries and performs actions on the data in the database.
Match each constraint with its definition: PRIMARY KEY FOREIGN KEY UNIQUE CHECK NOT NULL Definitions: A) Ensures that the column contains only distinct, non-null values. B) Ensures that the value in a column meets a specific condition C) Ensures that a column does not contain any NULL values. D) Ensures that the value in a column matches values in another table, maintaining referential integrity. E) Ensures that each row has a unique identifier, and the column cannot contain NULL values.
PRIMARY KEY → E) Ensures that each row has a unique identifier, and the column cannot contain NULL values. FOREIGN KEY → D) Ensures that the value in a column matches values in another table, maintaining referential integrity. UNIQUE → A) Ensures that the column contains only distinct, non-null values. CHECK → B) Ensures that the value in a column meets a specific condition NOT NULL → C) Ensures that a column does not contain any NULL values.
Explain general syntax of select statement.
SELECT * FROM users; SELECT is keyword for select statement * returns all columns for table (can replace with columns you want to return) FROM users specifies the table we are querying
Explain Having Clause syntax:
SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1 HAVING condition; SELECT: Specifies the columns to retrieve, including aggregate functions (e.g., SUM(), COUNT()). FROM: Indicates the table where the data resides. GROUP BY: Groups rows that have the same values in specified columns. HAVING: Filters the grouped data based on a condition applied to aggregate functions.
Explain syntax of scalar subquery:
SELECT column1, column2 FROM table1 WHERE column1 = ( SELECT expression FROM table2 WHERE condition );
Explain correlated subquery syntax:
SELECT column1, column2 FROM table1 outer WHERE column1 = ( SELECT column3 FROM table2 inner WHERE inner.column4 = outer.column5 ); outer and inner keywords are optional - simply differentiate between inner and outer queries
Explain EXCEPT Statement syntax:
SELECT column1, column2, ... FROM table1 EXCEPT SELECT column1, column2, ... FROM table2;
Explain syntax for an inner join:
SELECT column1, column2, ... FROM table1 INNER JOIN table2 ON table1.column = table2.column; SELECT column1, column2, ...: The columns you want to retrieve from both tables. FROM table1: The first table that you're joining. INNER JOIN table2: Specifies the second table that you're joining with the first table. ON table1.column = table2.column: The condition that defines how the two tables are related, typically by matching a common column in both tables (usually a primary key and foreign key). table1.column (often in the first table) is the foreign key. table2.column (often in the first table) is the primary key.
Explain INTERSECT syntax:
SELECT column1, column2, ... FROM table1 INTERSECT SELECT column1, column2, ... FROM table2;
Explain syntax of Union:
SELECT column1, column2, ... FROM table1 UNION SELECT column1, column2, ... FROM table2;
Explain syntax of UNION ALL:
SELECT column1, column2, ... FROM table1 UNION ALL SELECT column1, column2, ... FROM table2;
Explain syntax of a subquery:
SELECT column1, column2, ... FROM table1 WHERE column3 = (SELECT column FROM table2 WHERE condition); Outer Query: The main query that retrieves the final result. Subquery: The query inside the parentheses that is executed first, and its result is passed to the outer query.
Explain ORDER BY syntax:
SELECT column1, column2, ... FROM table_name WHERE condition ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...; SELECT: Specifies the columns you want to retrieve. FROM: Specifies the table where the data is coming from. WHERE: (Optional) Filters records based on specific conditions before sorting. ORDER BY: Keyword used to sort the result set. column1, column2, ...: The columns by which the data will be sorted. You can sort by multiple columns. Ascending is the default order. You can sort by multiple columns. When sorting by multiple columns, SQL will first sort by the first column; if there are ties (same values), it will then sort by the second column, and so on.
Explain syntax of WHERE clause:
SELECT column1, column2, ... FROM table_name WHERE condition; SELECT: Specifies the columns you want to retrieve. FROM: Specifies the table where you want to retrieve the data from. WHERE: This is the keyword used to filter rows based on the condition that follows. condition: This is the logical expression that defines the criteria for selecting rows. It can involve column values, comparison operators, logical operators, and pattern matching.\ Conditions in the WHERE clause can use various operators like =, >, <, >=, <=, <> (not equal), BETWEEN, IN, LIKE, IS NULL, etc. Multiple conditions can be combined using AND or OR. Parentheses () can be used to group conditions when combining them.
Explain LIMIT syntax:
SELECT column1, column2, ... FROM table_name [WHERE condition] [ORDER BY column_name [ASC|DESC]] LIMIT number_of_rows; WHERE and ORDER BY statements are optional LIMIT goes at the end of SELECT statement The LIMIT clause is typically used at the very end of a SELECT statement.
Explain GROUP BY syntax:
SELECT column1, column2, AGGREGATE_FUNCTION(column3) FROM table_name WHERE condition GROUP BY column1, column2; SELECT: Specifies the columns to retrieve from the table. AGGREGATE_FUNCTION(column): An aggregate function such as COUNT, SUM, AVG, MAX, or MIN that performs calculations on grouped data. FROM: Specifies the table from which to retrieve data. WHERE: (Optional) Filters records based on a specific condition before grouping. The WHERE clause is applied before the grouping. GROUP BY column1, column2, ...: Groups the result set based on one or more columns. All non-aggregated columns in the SELECT statement must be included in the GROUP BY clause. Columns listed in the SELECT statement (that are not used with aggregate functions) must appear in the GROUP BY clause. The GROUP BY clause is typically used after the WHERE clause (if present) and before the ORDER BY clause.
Explain the syntax for an outer join:
SELECT columns FROM table1 (LEFT/RIGHT/FULL) OUTER JOIN table2 ON table1.column = table2.column; table1 is the left table. table2 is the right table. The ON clause defines the condition for the join, typically matching a foreign key with a primary key.
How does SQL interact with the Database Management System (DBMS)?
SQL interacts with a Database Management System (DBMS) by serving as the language that allows users to communicate with the database to manage and manipulate the data.
What is SQL?
SQL stands for Structured Query Language. SQL is a tool for organizing, managing, and retrieving data stored by a computer relational database
What is TOP in SSMS equivalent to? What is it's syntax?
TOP is equivalent to LIMIT clause. Here is syntax: SELECT TOP (number_of_rows) column1, column2, ... FROM table_name [WHERE condition] [ORDER BY column_name [ASC|DESC]]; TOP (number_of_rows): Specifies the maximum number of rows to return. Unlike LIMIT, TOP is placed immediately after the SELECT keyword.
Explain syntax of UPDATE SQL statement:
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; UPDATE table_name : Specifies the table you want to modify. SET : Specifies which column(s) will be updated with new values. column1 = value1, column2 = value2 : Specifies the columns and their new values. You can update one or more columns in the same statement, separating each column assignment with a comma. WHERE condition : Filters the rows that will be updated. This is a crucial part of the statement, as it determines which records should be modified. If you omit the WHERE clause, all rows in the table will be updated!
Explain how WHERE and HAVING clauses work with GROUP BY clause:
WHERE filters individual rows before the GROUP BY operation and cannot work with aggregate functions. HAVING filters groups after the GROUP BY operation and can work with aggregate functions.
