What SQL command is used to insert a new row into a table?
Correct answer:
C) INSERT
Explanation: The INSERT command is used to add a new row to a table. UPDATE modifies existing rows, SELECT retrieves data, and DELETE removes rows from a table.
What SQL command is used to insert a new row into a table?
Correct answer:
C) INSERT
Explanation: The INSERT command is used to add a new row to a table. UPDATE modifies existing rows, SELECT retrieves data, and DELETE removes rows from a table.
In relational algebra, which operation is used to combine two tables based on related columns?
Correct answer:
B) JOIN
Explanation: The JOIN operation combines two tables by matching rows based on related columns. UNION combines all rows of two tables, PROJECT selects specific columns, and RENAME changes the name of a table or column.
Which SQL clause is used to specify the condition for selecting rows?
Correct answer:
B) WHERE
Explanation: The WHERE clause is used to filter rows based on a specified condition. SELECT retrieves data, GROUP BY groups rows based on column values, and ORDER BY sorts the result set.
What is the purpose of the FOREIGN KEY in a database?
Correct answer:
D) Both a and c
Explanation: A FOREIGN KEY links two tables together and ensures referential integrity by enforcing that the value in one table corresponds to a valid value in another table. It does not uniquely identify rows, which is the role of the PRIMARY KEY.
In SQL, which keyword is used to combine all rows from two tables into a single result set?
Correct answer:
A) UNION
Explanation: The UNION keyword combines all rows from two tables into a single result set, removing duplicates. INTERSECT selects only the rows that are common between two tables, JOIN combines rows based on a relationship, and PRODUCT produces all combinations of rows from two tables.
Which SQL clause is used to group rows with the same values in specified columns?
Correct answer:
B) GROUP BY
Explanation: The GROUP BY clause groups rows that have the same values in specified columns. It is often used with aggregate functions to provide summary data. WHERE filters rows, HAVING filters groups, and ORDER BY sorts the result set.
Which SQL command is used to change the structure of an existing table, such as adding a new column?
Correct answer:
C) ALTER
Explanation: The ALTER command modifies the structure of an existing table, such as adding or deleting columns. INSERT adds rows, CREATE creates new tables, and DROP removes tables or databases.
In relational algebra, which operation selects rows that meet a specified condition?
Correct answer:
A) SELECT
Explanation: The SELECT operation in relational algebra is used to choose rows that meet a specified condition. PROJECT chooses columns, JOIN combines tables, and UNION combines rows from two tables.
What does the DISTINCT keyword do in an SQL query?
Correct answer:
C) Removes duplicate rows from the result set
Explanation: The DISTINCT keyword eliminates duplicate rows from the result set. WHERE filters rows, JOIN combines rows from multiple tables, and ORDER BY sorts the result set.
Which relational operation in SQL is used to compute functions over multiple rows, such as summing or counting values?
Correct answer:
A) AGGREGATE
Explanation: The AGGREGATE operation computes functions over multiple rows, such as SUM(), COUNT(), and AVG(). UNION merges two tables, JOIN combines rows, and PRODUCT lists all combinations of rows from two tables.
Which SQL function is used to return the absolute value of a number?
Correct answer:
C) ABS()
Explanation: The ABS() function returns the absolute value of a number. ROUND() rounds a number to a specified number of decimal places, POW() raises a number to a power, and SQRT() returns the square root.
Which SQL clause is used to sort the results of a query?
Correct answer:
C) ORDER BY
Explanation: The ORDER BY clause is used to sort the results of a query in ascending or descending order. WHERE filters rows, GROUP BY groups rows, and HAVING filters groups.
In a database, what is the function of the PRIMARY KEY?
Correct answer:
C) To uniquely identify each row in a table
Explanation: The PRIMARY KEY uniquely identifies each row in a table and ensures that no duplicate values exist. It does not link tables or store binary data.
Which SQL operator is used to search for a specified pattern in a column?
Correct answer:
A) LIKE
Explanation: The LIKE operator is used in SQL to search for a specified pattern in a column, often using wildcards like %. BETWEEN is used for range conditions, IN checks if a value is in a list, and EXISTS checks for the existence of rows in a subquery.
In a relational database, what is a JOIN operation used for?
Correct answer:
C) To combine rows from two or more tables
Explanation: The JOIN operation combines rows from two or more tables based on a related column. It is not used to insert, remove, or filter rows.
Which SQL function returns the square root of a number?
Correct answer:
C) SQRT()
Explanation: The SQRT() function returns the square root of a number. ROUND() rounds a number, POW() raises a number to a power, and ABS() returns the absolute value.
In SQL, which command is used to delete all rows from a table but keep the table structure?
Correct answer:
C) TRUNCATE
Explanation: The TRUNCATE command removes all rows from a table but keeps the table structure intact. DELETE can remove specific rows, DROP removes the entire table, and ALTER modifies the structure of a table.
What does the INTERSECT operator do in SQL?
Correct answer:
B) Selects rows that are common to two tables
Explanation: The INTERSECT operator selects rows that are common between two tables. UNION combines all rows, DISTINCT removes duplicates, and UNION ALL combines rows without removing duplicates.
In a relational database, which key refers to the primary key in another table?
Correct answer:
B) Foreign Key
Explanation: A Foreign Key refers to the primary key in another table and creates a relationship between the two tables. A Primary Key uniquely identifies rows, a Candidate Key is a potential primary key, and a Unique Key enforces uniqueness but does not reference another table.
What is the purpose of the HAVING clause in SQL?
Correct answer:
C) To filter groups after aggregation
Explanation: The HAVING clause is used to filter groups after they have been aggregated, typically used with GROUP BY. WHERE filters rows before aggregation, and ORDER BY sorts the result set.
In SQL, what is the difference between DELETE and TRUNCATE?
Correct answer:
A) DELETE removes specific rows, while TRUNCATE removes all rows
Explanation: The DELETE command removes specific rows based on a condition, while TRUNCATE removes all rows from a table without keeping any specific rows. Neither command removes columns or tables.
What is a self join in SQL?
Correct answer:
B) A join between a table and itself
Explanation: A self join occurs when a table is joined with itself. It is used to compare rows within the same table. It does not involve identical rows or eliminate duplicates.
Which clause in SQL is used to combine rows from two queries, without removing duplicates?
Correct answer:
B) UNION ALL
Explanation: The UNION ALL clause combines rows from two queries without removing duplicates. UNION removes duplicates, INTERSECT selects common rows, and JOIN combines rows based on related columns.
What type of memory loses its data when the power is turned off?
Correct answer:
A) Volatile memory
Explanation: Volatile memory, such as RAM, loses its data when the power is turned off. Non-volatile memory retains data without power.
Which SQL command is used to modify the data in an existing row?
Correct answer:
C) UPDATE
Explanation: The UPDATE command is used to modify the data in an existing row. INSERT adds new rows, SELECT retrieves data, and DELETE removes rows.
What does the MODULO function do in SQL?
Correct answer:
B) Returns the remainder of a division
Explanation: The MODULO function returns the remainder of a division. It is commonly used to check divisibility or cycle through a series of values.
In SQL, which keyword is used to limit the number of rows returned by a query?
Correct answer:
A) LIMIT
Explanation: The LIMIT keyword is used to specify the maximum number of rows to return in the result set. SELECT retrieves data, GROUP BY groups rows, and DISTINCT removes duplicate rows.
What does the LEFT JOIN do in SQL?
Correct answer:
B) Returns all rows from the left table and the matching rows from the right table
Explanation: A LEFT JOIN returns all rows from the left table and the matching rows from the right table. If there is no match, NULL values are returned for columns from the right table.
Which SQL function is used to combine multiple strings into one?
Correct answer:
A) CONCAT()
Explanation: The CONCAT() function combines multiple strings into one. SUBSTRING() extracts a portion of a string, LENGTH() returns the number of characters in a string, and TRIM() removes leading and trailing spaces.
What is the function of the EXISTS operator in SQL?
Correct answer:
B) Returns TRUE if a subquery returns at least one row
Explanation: The EXISTS operator returns TRUE if a subquery returns at least one row and FALSE if it does not return any rows.
What is a full join in SQL?
Correct answer:
B) A join that returns all rows from both tables, including non-matching rows
Explanation: A FULL JOIN returns all rows from both tables, with NULLs for non-matching rows. It ensures that no data from either table is excluded.
Which clause in SQL is used to filter rows based on an aggregate function?
Correct answer:
C) HAVING
Explanation: The HAVING clause filters rows based on the result of an aggregate function, such as SUM() or COUNT(). WHERE filters rows before aggregation, while GROUP BY groups rows and ORDER BY sorts them.
Which type of join returns only the rows that have matching values in both tables?
Correct answer:
B) INNER JOIN
Explanation: An INNER JOIN returns only rows that have matching values in both tables. FULL JOIN returns all rows from both tables, LEFT JOIN returns all rows from the left table, and CROSS JOIN returns the Cartesian product of two tables.
Which SQL function returns the natural logarithm of a number?
Correct answer:
A) LOG()
Explanation: The LOG() function returns the natural logarithm of a number. POW() raises a number to a power, SQRT() returns the square root, and ABS() returns the absolute value.
What is the purpose of the TRIM() function in SQL?
Correct answer:
B) To remove leading and trailing spaces from a string
Explanation: The TRIM() function removes leading and trailing spaces from a string. SUBSTRING() extracts a portion of a string, CONCAT() joins two strings, and LENGTH() returns the length of a string.
Which SQL function returns a random number between 0 and 1?
Correct answer:
A) RAND()
Explanation: The RAND() function generates a random number between 0 and 1. ABS() returns the absolute value, POW() raises a number to a power, and SQRT() returns the square root.
What is a cross join in SQL?
Correct answer:
C) A join that returns all combinations of rows from two tables
Explanation: A CROSS JOIN returns the Cartesian product of two tables, which means it combines every row of the first table with every row of the second table. It does not filter or eliminate duplicates.
Which SQL operator is used to check if a value is within a list of values?
Correct answer:
C) IN
Explanation: The IN operator is used to check if a value is within a list of values. LIKE is used for pattern matching, BETWEEN selects values within a range, and EXISTS checks if a subquery returns any rows.
In SQL, what is the purpose of the CREATE VIEW statement?
Correct answer:
A) To create a virtual table based on a query
Explanation: The CREATE VIEW statement creates a virtual table (view) based on the result of a query. It does not store data but provides a way to simplify complex queries. Temporary tables are created with a different statement.
Which SQL clause is used to remove duplicate rows from the result set?
Correct answer:
A) DISTINCT
Explanation: The DISTINCT clause is used to remove duplicate rows from the result set. GROUP BY groups rows, ORDER BY sorts rows, and WHERE filters rows based on a condition.
What is the purpose of the JOIN clause in SQL?
Correct answer:
C) To combine rows from multiple tables based on related columns
Explanation: The JOIN clause is used to combine rows from multiple tables based on a related column. It does not insert data, remove duplicates, or filter rows.
Which SQL operator is used to select values within a specific range?
Correct answer:
B) BETWEEN
Explanation: The BETWEEN operator is used to select values within a specific range. LIKE is used for pattern matching, IN checks if a value is within a list, and EXISTS checks if a subquery returns any rows.
In SQL, which keyword is used to rename a column in the result set?
Correct answer:
A) AS
Explanation: The AS keyword is used to rename a column in the result set. RENAME is not used for columns in query results, FROM specifies the table, and SELECT retrieves data.
What is the function of the COUNT() aggregate function in SQL?
Correct answer:
B) To return the number of rows
Explanation: The COUNT() function returns the number of rows in the result set. SUM() adds numeric values, AVG() calculates the average, and MAX() returns the maximum value.
Which SQL clause is used to remove rows that do not match a specified condition?
Correct answer:
A) WHERE
Explanation: The WHERE clause filters rows based on a specified condition. GROUP BY groups rows, ORDER BY sorts rows, and DISTINCT removes duplicate rows.
In SQL, what is a subquery?
Correct answer:
A) A query that is nested inside another query
Explanation: A subquery is a query that is nested inside another query, typically in the WHERE or FROM clause. It is used to perform additional filtering or calculations within the main query.
Which SQL clause is used to return only unique rows from the result set?
Correct answer:
A) DISTINCT
Explanation: The DISTINCT clause removes duplicate rows from the result set. GROUP BY groups rows based on column values, ORDER BY sorts rows, and WHERE filters rows.
Which SQL operator is used to check if a value exists in a subquery?
Correct answer:
B) EXISTS
Explanation: The EXISTS operator is used to check if a subquery returns any rows. If the subquery returns one or more rows, EXISTS evaluates to TRUE. IN checks if a value is within a list, LIKE is used for pattern matching, and BETWEEN checks if a value falls within a range.
In SQL, which command is used to create a new database?
Correct answer:
B) CREATE DATABASE
Explanation: The CREATE DATABASE command is used to create a new database. CREATE TABLE is used to create a new table, INSERT INTO adds data to a table, and ALTER DATABASE modifies the structure of a database.
What does the HAVING clause do in SQL?
Correct answer:
B) Filters groups after aggregation
Explanation: The HAVING clause is used to filter groups after aggregation, typically used with GROUP BY. WHERE filters rows before aggregation, ORDER BY sorts the result set, and JOIN combines rows from two tables.
If this exam is giving you a hard time, let us be your helping hand. Contact us, and we'll provide the assistance you need to pass and move forward.