SQL Quiz (200 Questions) – Analytics Engineering

SQL Quiz (Questions 1–20)

Section 1: Basic SQL Concepts (1–10)

1. What does SQL stand for?

  Structured Query Language

  Simple Query Layout

  Standard Quick Lookup

  Sorted Queued Lists

2. Which part of SQL is primarily used to retrieve data?

  SELECT

  UPDATE

  DELETE

  ALTER

3. SQL is programming language for storing and processing information in?

  Relational databases

  Flat text files only

  Image files

  XML documents only

4. Which keyword begins a query that retrieves data from a table?

  FETCH

  SELECT

  GRANT

  MERGE

5. SQL is considered what type of language?

  Procedural only

  Object-oriented

  Declarative

  Assembly-level

6. Which of the following is a common relational database system?

  MongoDB

  PostgreSQL

  Redis

  Cassandra

7. Which keyword removes duplicates in a SELECT statement?

  UNIQUE

  DISTINCT

  LIMIT

  RENAME

8. Which statement changes data in a database?

  SELECT

  UPDATE

  CREATE

  DROP

9. In SQL, which symbol is commonly used as a wildcard in strings?

10. Which command is used to remove rows from a table?

  INSERT

  DELETE

  CREATE

  JOIN

11. Which clause specifies the source table(s) in a query?

  FROM

  WHERE

  GROUP BY

  HAVING

12. Which keyword selects all columns from a table?

  ALLCOLUMNS

  SHOW

  LIST

13. To select a literal string "Hello", you can write:

  SELECT Hello;

  SELECT 'Hello';

  SELECT "Hello" FROM Dual;

  SELECT LITERAL(Hello);

14. To rename a column in the result set, use:

  ALIAS keyword

  AS keyword

  CHANGE keyword

  MODIFY keyword

15. If you omit FROM, selecting from where is possible in standard SQL?

  FROM is always required in standard SQL

  Implicit from system table

  FROM keyword replaced by WITH

  Use SELECT only

16. To choose columns "name" and "age" from table "persons":

  SELECT persons.name, persons.age FROM persons;

  SELECT name, age ON persons;

  SELECT name, age OF persons;

  SELECT name & age FROM persons;

17. A SELECT statement without a WHERE clause returns:

  No rows

  Only distinct rows

  All rows

  An error

18. To avoid specifying a table alias, you can:

  Must always use alias

  Just write the table name

  Use a colon before columns

  Use a special keyword NOALIAS

19. Which of these is a valid SELECT syntax?

  SELECT col1 col2 FROM table;

  SELECT col1, col2 FROM table;

  SELECT (col1)(col2) FROM table;

  SELECT ; FROM table;

20. Using SELECT without specifying columns returns:

  Syntax error

  All columns (SELECT *)

  Only primary key columns

  Only first column

21. The WHERE clause is used to:

  Sort results

  Filter rows based on a condition

  Group results

  Join tables

22. To select rows where age > 18:

  WHERE age < 18

  WHERE age = 18

  WHERE age > 18

  WHERE age != 18

23. To match rows where name starts with 'A':

  WHERE name = 'A*'

  WHERE name LIKE 'A%'

  WHERE name LIKE '*A'

  WHERE name STARTSWITH 'A'

24. To select rows where city is either 'Paris' or 'London':

  WHERE city = 'Paris' AND 'London'

  WHERE city = ANY('Paris','London')

  WHERE city IN ('Paris','London')

  WHERE city LIKE ('Paris','London')

25. The WHERE clause is placed:

  After FROM

  Before FROM

  After ORDER BY

  At the very start of query

26. To find rows where salary is NOT 5000:

  WHERE salary = 5000

  WHERE salary LIKE 5000

  WHERE salary > 5000 ONLY

  WHERE salary <> 5000

27. Which operator checks for NULL values?

  IS NULL

  = NULL

  == NULL

  LIKE NULL

28. To exclude rows with age = 30:

  WHERE age EQUALS NOT 30

  WHERE age DIFFERENT 30

  WHERE age IS NOT 30

  WHERE age <> 30

29. To use a pattern to filter rows containing 'abc' anywhere in name:

  WHERE name = '%abc%'

  WHERE name LIKE 'abc'

  WHERE name LIKE '%abc%'

  WHERE name '%abc%'

30. To filter rows where price is between 10 and 20 inclusive:

  WHERE price BETWEEN 10 AND 20

  WHERE price BOUND 10 TO 20

  WHERE price >10 <20

  WHERE price IN RANGE(10,20)

31. Which function returns the number of rows?

  SUM()

  COUNT()

  AVG()

  MIN()

32. SUM() function is used to:

  Count rows

  Return minimum value

  Return total of numeric column

  Return average

33. MIN() function returns:

  The smallest value in a column

  The largest value

  The average value

  The sum of values

34. MAX() function returns:

  The smallest value

  The largest value

  The average value

  The count of rows

35. AVG() function returns:

  Sum of values

  Number of rows

  Average of numeric values

  First row only

36. COUNT(*) differs from COUNT(column) by:

  COUNT(*) counts all rows including NULLs

  COUNT(column) includes NULLs

  No difference

  COUNT(*) excludes duplicates automatically

37. To get total sales from 'orders' table with 'amount' column:

  SELECT SUM(amount) FROM orders;

  SELECT COUNT(amount) FROM orders;

  SELECT MIN(amount) FROM orders;

  SELECT AVG(amount) FROM orders;

38. To find the lowest salary in employees table:

  SELECT SUM(salary) FROM employees;

  SELECT AVG(salary) FROM employees;

  SELECT MIN(salary) FROM employees;

  SELECT COUNT(salary) FROM employees;

39. To count rows ignoring NULL in column 'age':

  SELECT COUNT(age) FROM table;

  SELECT COUNT(*) FROM table;

  SELECT COUNT(DISTINCT age) FROM table;

  SELECT SUM(age) FROM table;

40. Which function can remove duplicates when counting distinct values?

  COUNT(DISTINCT column)

  MIN(DISTINCT column)

  AVG(DISTINCT column)

  SUM(DISTINCT column)

41. GROUP BY is used to:

  Sort results

  Aggregate rows by columns

  Filter rows

  Join tables

42. GROUP BY must be used with:

  No other clause

  Aggregate functions

  ORDER BY always

  DISTINCT keyword

43. HAVING clause filters rows after:

  GROUP BY

  ORDER BY

  WHERE

  SELECT

44. To find total sales per department:

  SELECT department, SUM(sales) FROM table GROUP BY department;

  SELECT department SUM(sales) FROM table;

  SELECT SUM(sales), department FROM table HAVING department;

  SELECT department FROM table GROUP BY SUM(sales);

45. HAVING is used to filter groups, for example groups with SUM(sales)>1000:

  ... WHERE SUM(sales)>1000

  ... HAVING SUM(sales)>1000

  ... FILTER SUM(sales)>1000

  ... ORDER BY SUM(sales)>1000

46. If a SELECT uses GROUP BY, columns not aggregated must be:

  In GROUP BY clause

  In HAVING clause

  In WHERE clause

  Anywhere without rules

47. Average salary by job_title but only show groups with avg>5000:

  SELECT job_title, AVG(salary) FROM employees GROUP BY job_title HAVING AVG(salary)>5000;

  ... WHERE AVG(salary)>5000 GROUP BY job_title;

  ... HAVING AVG(salary)>5000 WITHOUT GROUP BY;

  ... GROUP BY job_title FILTER avg>5000;

48. Without GROUP BY, HAVING acts like WHERE, but standard SQL requires:

  GROUP BY when using HAVING

  HAVING alone is fine

  HAVING before WHERE

  Only one aggregate function

49. Count employees per department and only show departments with count>10:

  SELECT department, COUNT(*) FROM emp GROUP BY department HAVING COUNT(*)>10;

  ... HAVING COUNT(*)>10 GROUP BY department;

  ... GROUP BY COUNT(*)>10;

  ... WHERE COUNT(*)>10 GROUP BY department;

50. You cannot use HAVING without what?

  ORDER BY

  DISTINCT

  GROUP BY

  FROM

51. ORDER BY is used to:

  Filter rows

  Sort the result set

  Join multiple tables

  Aggregate data

52. By default ORDER BY sorts in:

  Descending order

  Ascending order

  Random order

  By primary key

53. To sort by name ascending and age descending:

  ORDER BY name ASC, age DESC

  ORDER BY name UP, age DOWN

  ORDER BY name, -age

  ORDER BY ASC name, DESC age

54. ORDER BY must come:

  Before FROM

  After WHERE, GROUP BY, HAVING but before LIMIT

  At the start of SELECT

  Immediately after SELECT

55. To sort by a column not in SELECT, generally:

  Not allowed

  Allowed if the column is from the table in FROM

  Must use GROUP BY

  Must use HAVING

56. To sort by multiple columns, separate by:

  Comma

  Semicolon

AND

OR

57. To get alphabetical order on 'name':

  ORDER BY name ASC

  ORDER BY name DESC

  GROUP BY name

  HAVING name

58. To reverse the sorting order:

  Use DESC keyword

  Use MINUS keyword

  Use FLIP keyword

  Not possible

59. If no ORDER BY specified, order of rows is:

  Guaranteed by insertion order

  Unspecified/not guaranteed

  Always ascending by primary key

  Always descending

60. ORDER BY can sort by column position using:

  Column indexes like ORDER BY 2

  ORDER BY #2

  ORDER BY position('2')

  Not allowed

61. INNER JOIN returns:

  All rows from left table only

  All rows from both tables regardless of match

  Only matched rows in both tables

  No rows if unmatched

62. LEFT JOIN returns:

  Matched rows plus unmatched from left table

  Matched plus unmatched from right table

  Only matched rows

  Cartesian product

63. RIGHT JOIN returns:

  Matched rows plus unmatched from right table

  Matched plus unmatched from left table

  Only matched rows

  Same as INNER JOIN

64. FULL OUTER JOIN returns:

  Only matched rows

  Matched plus unmatched from both tables

  Only unmatched rows

  Only left table rows

65. To join 'orders' and 'customers' where orders.cust_id=customers.id:

  SELECT ... FROM orders JOIN customers ON orders.cust_id=customers.id;

  SELECT ... FROM orders, customers = orders.cust_id=customers.id;

  SELECT ... FROM orders JOIN customers.id=orders.cust_id;

  SELECT ... FROM orders & customers WHERE join;

66. INNER JOIN is also called:

  Just JOIN or equi-join

  Anti-join

  Semi-join

  Cross join

67. To get all customers even if they have no orders:

  Use LEFT JOIN on customers

  Use INNER JOIN

  Use FULL JOIN only

  Use CROSS JOIN

68. CROSS JOIN returns:

  Cartesian product of both tables

  Only matched rows

  No rows

  Only one row

69. To filter joined results, use conditions in:

  JOIN clause only

  WHERE clause after join

  HAVING without GROUP BY

  ORDER BY only

70. If you want all rows from both tables with no match replaced by NULLs:

  FULL OUTER JOIN

  LEFT JOIN

  RIGHT JOIN

  INNER JOIN

71. A subquery is:

  A query that must be in GROUP BY

  A query that can only appear at the end

  A query nested inside another query

  A query that never returns rows

72. Subqueries are often used in:

  WHERE or HAVING clauses

  ORDER BY only

  SELECT * only

  In the DDL commands only

73. A scalar subquery returns:

  Multiple columns

  A single value

  An entire table

  Exactly two rows

74. To find rows with value greater than the average, use:

  WHERE col >= AVG()

  WHERE col > col

  WHERE col > (SELECT AVG(col) FROM table)

  GROUP BY AVG(col)

75. A derived table is a subquery used:

  Only in JOIN clauses

  In the FROM clause as a virtual table

  In WHERE only

  After ORDER BY

76. A subquery returning multiple values can be used with:

  IN operator

  Equals (=) always

  More than clause

  GROUP BY

77. Correlated subqueries:

  Don't exist

  Are subqueries that run first

  Return only one row always

  Reference columns from the outer query

78. To use a subquery as a column list:

  Not allowed

  Must ensure subquery returns one value

  Use HAVING

  Use FULL JOIN

79. Subqueries can appear in:

  SELECT, FROM, WHERE clauses

  Only SELECT

  Only WHERE

  Only FROM

80. A derived table must have:

  No alias

  GROUP BY inside

  An alias to be referenced

  A LIMIT always

81. UNION combines:

  One table into two

  Columns side-by-side

  Result sets of two queries into one set

  Only distinct columns

82. UNION requires:

  Same number of rows

  Same number and type of columns in both queries

  Identical table names

  GROUP BY in each query

83. UNION by default removes duplicates. To keep duplicates use:

  UNION UNIQUE

  UNION DISTINCT

  UNION ALL

  UNION DUP

84. INTERSECT returns:

  All rows from first query only

  All rows from second query only

  Union of both queries

  Rows common to both queries

85. EXCEPT returns:

  Intersection of two sets

  Union of both sets

  Rows in first query not in second

  Rows in second query not in first

86. Set operations typically require:

  Same number of columns and compatible data types

  Identical table names

  GROUP BY in each query

  DISTINCT keyword always

87. UNION ALL vs UNION differs by:

  UNION ALL removes duplicates

  UNION ALL keeps duplicates, UNION removes duplicates

  UNION ALL sorts results

  UNION ALL requires GROUP BY

88. INTERSECT and EXCEPT might not be supported by all SQL dialects. A common replacement for INTERSECT is:

  Using JOIN and WHERE conditions

  GROUP BY to find matches

  UNION DISTINCT again

  USING FULL OUTER JOIN

89. Set operators combine results of multiple SELECTs. The correct syntax usually is:

  SELECT ... INTERSECT SELECT ... ORDER BY first

  SELECT ... EXCEPT SELECT ... GROUP BY last

  (SELECT ... ) UNION (SELECT ... ) [ORDER BY at end]

  SELECT ... JOIN SELECT

90. EXCEPT is sometimes known as:

  UNION MINUS

  MINUS in some SQL dialects

  INTERSECT ALL

  UNION DEFAULT

91. CREATE TABLE is used to:

  Insert rows into table

  Update an existing table

  Define a new table structure

  Delete a table

92. DROP TABLE does what?

  Removes only data

  Removes table structure and data

  Removes only indexes

  Just hides the table

93. ALTER TABLE is used to:

  Add, drop, or modify columns

  Insert new rows

  Change data in rows

  Rename the database

94. To remove a column from a table:

  DROP COLUMN via SELECT

  DELETE column in DML

  ALTER TABLE table_name DROP COLUMN col_name

  CREATE OR REPLACE

95. CREATE TABLE syntax generally includes:

  Just table name

  Table name and column definitions

  Data insert statements

  ORDER BY clause

96. To rename a table:

  ALTER TABLE old_name RENAME TO new_name

  SELECT * FROM old_name RENAME new_name

  DROP old_name AND CREATE new_name

  UPDATE TABLE SET name='new_name'

97. To add a new column:

  CREATE COLUMN

  ALTER TABLE ... ADD column_name datatype

  INSERT INTO table (column_name)

  SELECT column_name NEW

98. DDL statements like CREATE, DROP, ALTER usually:

  Need no commit in all databases

  Always prompt user input

  Implicitly commit transactions in many DB systems

  Never allowed in SQL

99. To completely remove a table and its data:

  DROP TABLE table_name

  DELETE FROM table_name

  TRUNCATE TABLE

  REMOVE TABLE

100. To remove all rows but keep table structure:

  DROP TABLE

  DELETE TABLE

  ALTER TABLE CLEAR

  TRUNCATE TABLE

101. INSERT is used to:

  Modify existing rows

  Remove rows

  Add new rows

  Create a new table

102. UPDATE changes:

  Table structure

  Existing rows' data

  Permissions

  Drops columns

103. DELETE removes:

  Rows based on a condition

  Table structure

  Indexes

  Column definitions

104. INSERT INTO table (col1, col2) VALUES (val1, val2):

  Adds a new row with col1=val1, col2=val2

  Modifies existing row

  Deletes rows

  Creates a new table

105. To update a row's salary in employees:

  INSERT salary=... INTO employees

  ALTER TABLE employees SET salary

  CREATE TABLE employees SALARY=...

  UPDATE employees SET salary=... WHERE condition

106. DELETE FROM table WHERE condition:

  Removes the table

  Removes rows matching condition

  Updates rows

  Creates a backup

107. To insert multiple rows at once (some dialects allow):

  INSERT ONE ROW ONLY

  UPDATE MULTIPLE VALUES

  INSERT INTO table VALUES (..),(..),(..)

  CREATE OR REPLACE

108. Without WHERE in UPDATE or DELETE:

  All rows are affected

  No rows change

  Error occurs

  Updates primary key only

109. INSERT can be combined with SELECT to:

  Insert rows from another table

  Only insert default row

  Update existing data

  Create a new database

110. TRUNCATE TABLE differs from DELETE because:

  TRUNCATE adds rows

  TRUNCATE removes all rows without logging each row deletion

  DELETE always faster

  TRUNCATE is DML, DELETE is DDL

111. PRIMARY KEY ensures:

  Column can be NULL

  Uniqueness and non-nullness of a column or set of columns

  Only uniqueness, null allowed

  Foreign references only

112. FOREIGN KEY enforces:

  Primary key uniqueness

  Referential integrity between tables

  Check constraints only

  Non-null only

113. UNIQUE constraint ensures:

  Column must be NULL

  Column has no constraints

  No duplicate values in that column

  Column auto increments

114. NOT NULL ensures:

  Column cannot have NULL values

  Column must have duplicates

  Column always numeric

  Column is primary key

115. CHECK constraint allows:

  Only one row

  Foreign keys only

  A condition to limit values in a column

  Dropping columns

116. A table can have how many PRIMARY KEY constraints?

  Unlimited

  Only one PRIMARY KEY

  None allowed

  Two per column

117. FOREIGN KEY references:

  A column in same table always

  A unique constraint always in same table

  A column or columns in another table (usually primary key)

  Only non-null columns

118. UNIQUE differs from PRIMARY KEY in that:

  UNIQUE must allow NULL always

  PRIMARY KEY can allow multiple NULLs

  They are identical

  UNIQUE allows NULL values (often one NULL), PRIMARY KEY does not

119. To ensure age > 0 in a column, use:

  CHECK(age > 0)

  FOREIGN KEY ON age

  PRIMARY KEY ON age

  NOT NULL only

120. Constraints are defined usually in:

  INSERT statements

  DDL statements (CREATE/ALTER TABLE)

  SELECT queries

  Within WHERE clauses

121. A VIEW is:

  A physical copy of data

  A stored SELECT query that acts like a virtual table

  Always updatable in all databases

  A type of index

122. CREATE VIEW syntax includes:

  CREATE VIEW view_name AS table_name;

  CREATE VIEW with no SELECT

  CREATE VIEW view_name AS SELECT ...

  CREATE VIEW view_name ONLY;

123. A MATERIALIZED VIEW is:

  Same as a normal view

  A view that stores the query results physically

  A synonym for an index

  A type of constraint

124. Views can be used to:

  Always improve performance over base tables

  Enforce primary keys

  Simplify complex queries and provide security abstraction

  Replace transactions

125. To refresh a materialized view (in systems that support it):

  SELECT * FROM the view

  ALTER VIEW REFRESH

  Truncate the base table

  Use a REFRESH MATERIALIZED VIEW command

126. Dropping a view:

  Deletes underlying data

  Only removes the virtual definition

  Is not possible

  Alters base table

127. Some views are updatable if:

  They reference a single base table and certain conditions are met

  They contain aggregates always

  They are materialized views

  They contain DISTINCT

128. A view does not physically store data except:

  All views store data

  When using indexed views in all databases

  In the case of materialized views

  When using a foreign key

129. To ensure view runs a complex query efficiently without recalculating each time, use:

  Normal view only

  A foreign key

  INDEX on normal view

  Materialized view

130. Views can be used for security by:

  Removing all data

  Restricting user access to certain columns/rows via the view

  Storing passwords

  Encrypting the base table

131. An index is used to:

  Speed up data retrieval

  Ensure uniqueness only

  Store backups

  Sort table permanently

132. A primary key usually creates:

  No index

  An index automatically

  Foreign key automatically

  View automatically

133. Adding too many indexes can:

  Always improve performance

  Have no effect

  Only benefit updates

  Degrade performance on INSERT/UPDATE/DELETE due to overhead

134. A clustered index:

  Is supported by all SQL dialects

  Determines the physical order of data in the table

  Is the same as a view

  Prevents NULL values

135. Non-clustered index stores:

  Data physically reordered

  Only keys with no reference to data

  A separate structure referencing the table data by key

  Backups

136. To analyze query performance, one might use:

  EXPLAIN or EXPLAIN PLAN

  SELECT PERFORMANCE MODE

  HAVING COUNT(PLAN)

  CREATE INDEX ALWAYS

137. Indexes help SELECT queries but can slow down:

  SELECT as well

  DROP TABLE

  CREATE VIEW

  INSERT, UPDATE, DELETE operations

138. A good candidate for indexing is:

  A column never used in WHERE or JOIN

  A column frequently used in search conditions

  A column with all identical values

  A rarely queried column

139. Removing unused indexes can:

  Improve write performance

  Slow down reads

  Cause syntax errors

  Create duplicates

140. Indexes are created by:

  SELECT command

  CREATE TABLE only

  INSERT INDEX

  CREATE INDEX command

141. A window function is defined using:

  GROUP BY only

  JOIN clause

  OVER(...) clause

  HAVING clause

142. Window functions allow calculations:

  Only on entire table

  Across a set of rows defined by a partition, without collapsing to one row

  That replace GROUP BY

  Only after ORDER BY

143. PARTITION BY in window functions:

  Divides result set into partitions

  Replaces WHERE

  Is mandatory

  Not allowed with OVER

144. ROW_NUMBER() function:

  Counts rows like COUNT()

  Summarizes data

  Requires GROUP BY

  Assigns a sequential number to each row in a partition

145. RANK() vs ROW_NUMBER():

  RANK gives same rank to ties, ROW_NUMBER gives unique sequence

  They are identical

  RANK can only be used without PARTITION BY

  ROW_NUMBER creates groups

146. LAG() and LEAD() functions allow:

  Summation of all rows

  Accessing preceding or following row values

  Checking constraints

  Creating indexes

147. NTILE(n) divides rows into:

  Groups of equal values

  Percentiles only

  n buckets with nearly equal number of rows

  Binary halves

148. To use window functions, OVER clause is mandatory because:

  It defines the window partitioning and ordering

  It replaces SELECT

  It is optional

  It creates a view

149. Window functions do not collapse rows like GROUP BY. They:

  Remove duplicates

  Only compute sums

  Require DISTINCT

  Return a value for each row after computation

150. ORDER BY inside OVER(...) defines:

  The sequence for the window function's calculation

  Sorting of entire result set necessarily

  No effect

  Aggregation rules

151. A CTE starts with:

  USING CTE

  WITH clause

  CREATE TEMP TABLE

  CTE SELECT

152. A CTE is defined for:

  The query that immediately follows the WITH clause

  All future queries

  Until commit

  Until DROP CTE

153. CTEs can be used to:

  Replace indexes

  Commit transactions

  Simplify complex queries and improve readability

  Enforce foreign keys

154. A CTE resembles a derived table, but:

  Must be materialized

  Cannot reference itself

  Is permanent

  Is defined before the main query and can be referenced multiple times

155. Recursive CTEs allow:

  Only simple queries

  Traversing hierarchical data by referencing the CTE within itself

  Updating DDL

  Creating indexes

156. Syntax of a basic CTE:

  WITH cte_name AS (SELECT ...) SELECT ... FROM cte_name;

  CTE cte_name = SELECT ...;

  CREATE CTE cte_name;

  INSERT CTE ...

157. Multiple CTEs can be defined by:

  Multiple WITH statements

  Only one CTE per query allowed

  Comma separating each CTE after WITH

  Using CREATE CTE repeatedly

158. CTEs are not stored persistently; they are:

  Temporary within the single query execution

  Saved as a view

  Indexed automatically

  Materialized by default

159. Recursive CTEs need:

  A table reference inside itself only

  An anchor member (base query) and a recursive member

  Just one SELECT

  A primary key constraint

160. CTEs improve readability compared to:

  Nested subqueries

  Index usage

  DDL statements

  Triggers

161. String functions like LENGTH() or CHAR_LENGTH() return:

  Numeric sums

  A substring always

  The number of characters in a string

  Date intervals

162. SUBSTRING(string, start, length) extracts:

  A portion of the string

  Only first character

  Numeric sum

  Updates the table

163. UPPER() and LOWER() functions change string case to:

  Numeric only

  Date format

  Random case

  Uppercase or lowercase respectively

164. Date functions like NOW() or CURRENT_DATE():

  Return numeric sums

  Return length of a string

  Return current date/time

  Create indexes

165. EXTRACT(YEAR FROM date) returns:

  The year part of a date

  The month only

  Number of days in the year

  Always NULL

166. DATEADD or ADD_MONTHS are used to:

  Compare strings

  Add an interval to a date

  Convert date to string

  Remove columns

167. CASE expression allows:

  Running DDL inside SELECT

  Only numeric comparisons

  Conditional logic inside a SELECT statement

  Grouping by conditions

168. CASE WHEN condition THEN value ELSE other END:

  Returns 'value' if condition is true, otherwise 'other'

  Forces a join

  Must appear in GROUP BY

  Inserts a new row

169. String functions like CONCAT(s1,s2):

  Returns numeric sum

  Splits string

  Always returns uppercase

  Joins two strings together

170. COALESCE(expr1, expr2, ...) returns:

  NULL always

  The sum of all expressions

  The first non-NULL expression

  The last non-NULL expression

171. A stored procedure is:

  Always returns a value like a function

  Only a SELECT statement

  A compiled set of SQL statements stored in the database

  A type of index

172. A stored function differs from a procedure by:

  Not returning any value

  Returning a single value

  Having no parameters

  Always altering the schema

173. CREATE PROCEDURE syntax varies by dialect, but generally includes:

  CREATE PROCEDURE proc_name(param...) AS BEGIN ... END

  CREATE PROC with no body

  CREATE PROCEDURE inside SELECT

  CREATE PROCEDURE with DROP

174. Stored procedures can improve performance by:

  Disabling indexes

  Removing constraints

  Precompiling and caching execution plans

  Always slower than direct queries

175. Functions are often used in SELECT statements because:

  They can update the table directly

  They return a value that can be used per row

  They create indexes automatically

  They replace JOIN

176. Stored procedures can accept IN, OUT parameters to:

  Pass values in and return values out

  Only pass values in

  Only return values

  Enforce uniqueness

177. In PL/SQL or T-SQL, you can write:

  Only DDL

  Only SELECT

  Procedural code with loops, ifs, variables

  Only INDEX commands

178. To drop a stored procedure:

  DELETE PROCEDURE name

  ALTER PROCEDURE name DROP

  CREATE PROCEDURE name OVERWRITE

  DROP PROCEDURE proc_name

179. Stored functions must:

  Return a single value

  Not return anything

  Create indexes

  Only run once

180. Procedures and functions enhance:

  Table structure changes

  Automatic indexing

  Reusability and encapsulation of logic in the database

  Dropping constraints

181. A transaction is:

  Only read operations

  A sequence of operations treated as a single unit of work

  Always one SELECT

  Index creation only

182. COMMIT saves changes made during a transaction:

  And then rollbacks automatically

  Into a temporary area

  Permanently to the database

  Discards all changes

183. ROLLBACK undoes:

  All changes in the current transaction not yet committed

  Only SELECT queries

  DDL changes

  Cannot undo anything

184. BEGIN or START TRANSACTION begins:

  A DDL statement

  Index creation

  A new transactional context

  Ends a transaction

185. Isolation levels define:

  Character encoding

  How transactions interact and what concurrency phenomena are allowed

  Backup intervals

  The order of indexes

186. A common isolation level is:

  READ WRONG

  EXECUTE COMMIT

  WRITE-ONLY

  READ COMMITTED

187. Locking is used to prevent:

  SELECT statements

  Table creation

  Conflicts when multiple transactions access the same data

  Index usage

188. Deadlocks occur when:

  Two transactions hold locks the other needs

  No locks are used

  Only one transaction is running

  The table is empty

189. COMMIT ends a transaction and makes changes:

  Lost

  Permanent

  Temporary

  Reversible without ROLLBACK

190. SAVEPOINT allows:

  Automatic commits

  Creating indexes inside transaction

  Rolling back to a specific point in a transaction

  Dropping foreign keys

191. Partitioning a table means:

  Dropping half of the data

  Storing table data in separate pieces according to partition rules

  Always improves all queries

  Merging tables into one

192. Horizontal partitioning splits data by:

  Columns

  Data types

  Rows based on a value range or hash

  Permissions

193. Vertical partitioning splits data by:

  Columns into different physical tables

  Rows only

  Data types

  Index keys

194. Sharding is a form of partitioning where:

  Data remains on one server

  Only indexes are split

  It applies only to views

  Data is distributed across multiple servers

195. Caching query results in memory can:

  Slow down all queries

  Make data inconsistent

  Improve read performance for repeated queries

  Replace indexes

196. Query optimization involves:

  Removing all indexes

  Ensuring queries use the best execution plan

  Using only SELECT *

  Never analyzing queries

197. Table partitioning can improve performance by:

  Allowing queries to scan only relevant partitions

  Merging all data into one chunk

  Forcing full table scans always

  Removing primary keys

198. Parallel query execution means:

  Running queries sequentially only

  Using one CPU always

  Splitting the work of a single query across multiple CPU threads

  Disabling indexes

This quiz is for Premium Users only, purchase a premium membership here to access.

199. Compression in databases can:

Membership required
Membership required
Membership required
Membership required

This quiz is for Premium Users only, purchase a premium membership here to access.

200. Advanced partitioning strategies and indexing can lead to:

Membership required
Membership required
Membership required
Membership required

…

Ask Tutor

Tutor Chat