1Z0-071 Practice Test Video Answers

1. A  

Explanation: The correct syntax for selecting all records from the “employees” table where the “salary” is greater than 50000 is “SELECT * FROM employees WHERE salary > 50000;”.

2. B  

Explanation: The “ALTER TABLE” command is used to change the structure of an existing table, such as adding or dropping columns.

3. B  

Explanation: The “SUM()” function in SQL is used to calculate the total sum of a numeric column.

4. A  

Explanation: The default sorting order in SQL queries is ascending unless specified otherwise.

5. C  

Explanation: The “DISTINCT” keyword ensures that all records in the result set are unique, removing any duplicate rows.

6. A  

Explanation: The “WHERE” clause is used to specify conditions in a SQL query to filter records.

7. C  

Explanation: The “UNION” operator combines two or more SELECT queries and returns only unique records from both queries.

8. A  

Explanation: “INT” is a valid numeric data type in SQL that stores integer values.

9. D  

Explanation: “TRUNCATE TABLE” removes all rows from a table, but keeps the table structure intact.

10. B  

Explanation: The “CREATE TABLE” statement is used to create a new table in SQL.

11. A  

Explanation: The correct syntax to update a record in SQL is “UPDATE table_name SET column_name = new_value WHERE condition;”.

12. A  

Explanation: The “DROP DATABASE” statement is used to remove a database from the system, along with all its tables.

13. A  

Explanation: The “GROUP BY” clause is used to group rows that have the same values into summary rows, typically used with aggregate functions like COUNT(), SUM(), AVG(), etc.

14. B  

Explanation: The “AVG()” function calculates the average value of a numeric column.

15. A  

Explanation: The “HAVING” clause is used to filter records after grouping them using the “GROUP BY” clause.

16. A  

Explanation: The query “SELECT COUNT(*) FROM employees WHERE salary > 30000;” will return the total number of employees with a salary greater than 30000.

17. B  

Explanation: The correct syntax to add a new column to an existing table is “ALTER TABLE table_name ADD column_name TYPE;”.

18. A  

Explanation: The “COUNT()” function in SQL is used to count the number of rows in a table or group.

19. C  

Explanation: To remove a column from a table, the correct SQL command is “ALTER TABLE table_name DROP column_name;”.

20. B  

Explanation: The “LIKE” operator is used to search for a specified pattern in a column, typically with wildcard characters.

21. A  

Explanation: The “BETWEEN” operator is used to filter the result set based on a range of values, inclusive.

22. B  

Explanation: To find all employees whose names start with “J,” you would use the “LIKE” operator with a wildcard character: “name LIKE ‘J%’;”.

23. A  

Explanation: “INNER JOIN” combines records from two tables based on a related column, returning only those that have matching values in both tables.

24. B  

Explanation: The query “SELECT 5 + ’10’;” will result in an error because adding a number to a string type is not valid unless explicitly cast.

25. A  

Explanation: The statement “SELECT * FROM employees;” retrieves all columns from the “employees” table.

26. C  

Explanation: The “ORDER BY” clause is used to sort the result set in ascending or descending order.

27. A  

Explanation: The “INNER JOIN” clause in SQL returns only the rows that have matching values in both tables.

28. C  

Explanation: The function “NOW()” returns the current date and time in SQL.

29. A  

Explanation: The “COUNT(*)” function is used to count the total number of rows in the “employees” table.

30. C  

Explanation: The syntax to rename a column in SQL is “ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;”.

31. A  

Explanation: The “MAX()” function is used to find the highest value in a column.

32. A  

Explanation: The correct SQL statement to add a primary key to the “id” column in the “employees” table is “ALTER TABLE employees ADD PRIMARY KEY (id);”.

33. A  

Explanation: The syntax “ALTER TABLE table_name ADD FOREIGN KEY (column_name) REFERENCES other_table (other_column);” is used to add a foreign key to a column.

34. C  

Explanation: The “EXCEPT” keyword is used to exclude records from the result set that are present in another query’s result set.

35. A  

Explanation: The statement “UPDATE employees SET salary = 55000 WHERE id = 1;” correctly updates the “salary” column for the employee with “id” equal to 1.

36. C  

Explanation: “CREATE INDEX” creates an index to improve the performance of SQL queries by enabling faster data retrieval.

37. B  

Explanation: To retrieve the first five records from a table in SQL, the correct syntax is “SELECT * FROM employees LIMIT 5;”.

38. B  

Explanation: The “DISTINCT” keyword is used to eliminate duplicate rows from the result set.

39. B  

Explanation: The correct syntax to join the “orders” table with the “customers” table on the “customer_id” field is “SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id;”.

40. B  

Explanation: The query “SELECT * FROM employees ORDER BY id DESC LIMIT 1;” retrieves the last record from the “employees” table by sorting in descending order and limiting the result to one record.