SQL Test For Mappers

1.

Which SQL statement is used to delete data from a database?

REMOVE

COLLAPSE

DELETE

The correct answer is DELETE. DELETE is the SQL statement used to remove data from a database. This statement allows you to delete specific rows or all the rows from a table, depending on the condition specified in the WHERE clause. It is a crucial command for managing and maintaining the data integrity of a database.

Explanation

The correct answer is DELETE. DELETE is the SQL statement used to remove data from a database. This statement allows you to delete specific rows or all the rows from a table, depending on the condition specified in the WHERE clause. It is a crucial command for managing and maintaining the data integrity of a database.

2.

1.	What does SQL stand for?

Structured Question Language

Structured Query Language

Strong Question Language

SQL stands for Structured Query Language. It is a programming language used for managing and manipulating relational databases. With SQL, users can create, modify, and retrieve data from databases. It provides a standardized way to interact with databases, allowing users to write queries to retrieve specific information or perform actions such as inserting, updating, or deleting data. The correct answer, "Structured Query Language," accurately reflects the purpose and functionality of SQL.

Explanation

SQL stands for Structured Query Language. It is a programming language used for managing and manipulating relational databases. With SQL, users can create, modify, and retrieve data from databases. It provides a standardized way to interact with databases, allowing users to write queries to retrieve specific information or perform actions such as inserting, updating, or deleting data. The correct answer, "Structured Query Language," accurately reflects the purpose and functionality of SQL.

3. Which SQL statement is used to update data in a database?

MODIFY

SAVE

UPDATE

SAVE AS

The correct answer is UPDATE. The UPDATE statement is used in SQL to modify or update existing data in a database. It allows you to change the values of one or more columns in a table based on specified conditions. This statement is essential for making changes to data stored in a database, such as updating records with new information or correcting errors.

Explanation

The correct answer is UPDATE. The UPDATE statement is used in SQL to modify or update existing data in a database. It allows you to change the values of one or more columns in a table based on specified conditions. This statement is essential for making changes to data stored in a database, such as updating records with new information or correcting errors.

4. Which SQL statement is used to insert new data in a database?

INSERT NEW

ADD NEW

INSERT INTO

ADD RECORD

The correct answer is "INSERT INTO". This SQL statement is used to insert new data into a database. It allows you to specify the table where the data will be inserted and provide the values for the new record. The "INSERT INTO" statement is a fundamental part of SQL and is commonly used when adding new data to a database table.

Explanation

The correct answer is "INSERT INTO". This SQL statement is used to insert new data into a database. It allows you to specify the table where the data will be inserted and provide the values for the new record. The "INSERT INTO" statement is a fundamental part of SQL and is commonly used when adding new data to a database table.

5. Which SQL statement is used to extract data from a database?

SELECT

EXTRACT

GET

OPEN

The SELECT statement is used to extract data from a database. It allows you to specify which columns and rows you want to retrieve from a table or multiple tables. By using various clauses such as WHERE, ORDER BY, and JOIN, you can further refine the data that is returned. This statement is a fundamental part of SQL and is essential for querying and retrieving information from a database.

Explanation

The SELECT statement is used to extract data from a database. It allows you to specify which columns and rows you want to retrieve from a table or multiple tables. By using various clauses such as WHERE, ORDER BY, and JOIN, you can further refine the data that is returned. This statement is a fundamental part of SQL and is essential for querying and retrieving information from a database.

6. Which SQL statement is used to return only different values?

SELECT DIFFERENT

SELECT UNIQUE

SELECT DISTINCT

SELECT ALL

The SQL statement "SELECT DISTINCT" is used to return only different values. It eliminates duplicate values from the result set and only retrieves unique values. This is helpful when you want to retrieve distinct values from a column or multiple columns in a table.

Explanation

The SQL statement "SELECT DISTINCT" is used to return only different values. It eliminates duplicate values from the result set and only retrieves unique values. This is helpful when you want to retrieve distinct values from a column or multiple columns in a table.

7.

With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" starts with an "a"?

SELECT * FROM Persons WHERE FirstName LIKE 'a%'

SELECT * FROM Persons WHERE FirstName LIKE '%a'

SELECT * FROM Persons WHERE FirstName='%a%'

SELECT * FROM Persons WHERE FirstName='a'

The correct answer is "SELECT * FROM Persons WHERE FirstName LIKE 'a%'". This query uses the LIKE operator with the pattern 'a%' to match any values in the FirstName column that start with the letter "a". The '%' symbol is a wildcard that represents any number of characters. Therefore, this query will select all the records from the Persons table where the FirstName column starts with "a".

Explanation

The correct answer is "SELECT * FROM Persons WHERE FirstName LIKE 'a%'". This query uses the LIKE operator with the pattern 'a%' to match any values in the FirstName column that start with the letter "a". The '%' symbol is a wildcard that represents any number of characters. Therefore, this query will select all the records from the Persons table where the FirstName column starts with "a".

8.

How can you change "Hansen" into "Nilsen" in the "LastName" column in the Persons table?

MODIFY Persons SET LastName='Hansen' INTO LastName='Nilsen

UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen'

MODIFY Persons SET LastName='Nilsen' WHERE LastName='Hansen'

UPDATE Persons SET LastName='Hansen' INTO LastName='Nilsen'

The correct answer is "UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen'". This is the correct answer because it uses the UPDATE statement to modify the LastName column in the Persons table. It sets the value of LastName to 'Nilsen' for all rows where the current value of LastName is 'Hansen'.

Explanation

The correct answer is "UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen'". This is the correct answer because it uses the UPDATE statement to modify the LastName column in the Persons table. It sets the value of LastName to 'Nilsen' for all rows where the current value of LastName is 'Hansen'.

9. The Person table has the following structure :
Last_Name Varchar2(30);
Salary number(8,2);
Commision_PCT number (5,2).
You want to display the name and the annual salary multiplied by the commission_pct for all the employees. and the annual commission.Additionally records that have a null value for Commision_PCT a zero must be displayed against the calculated column.Which SQL statement display the desired results?

Select Last_Name,(salary*12)*NVL(Commision_PCT,0) FROM Person

Select Last_Name,(salary*12)*NVL2(Commision_PCT,0) FROM Person

Select Last_Name,(salary*12)* ifnull(Commision_PCT,0) FROM Person

Select Last_Name,(salary*12)*NVL(Commision_PCT,0) FROM Person

The correct answer is "select Last_Name,(salary*12)*NVL(Commision_PCT,0) FROM Person". This SQL statement uses the NVL function to replace any null values in the Commision_PCT column with 0 before multiplying it with the annual salary. This ensures that even if there are null values in the Commision_PCT column, the calculated column will still display a value of 0.

Explanation

The correct answer is "select Last_Name,(salary*12)*NVL(Commision_PCT,0) FROM Person". This SQL statement uses the NVL function to replace any null values in the Commision_PCT column with 0 before multiplying it with the annual salary. This ensures that even if there are null values in the Commision_PCT column, the calculated column will still display a value of 0.

10. Examine the data in the EMPLOYEES table: LAST_NAME DEPARTMENT_ID SALARY Getz 10 3000 Davis 20 1500 King 20 2200 Davis 30 5000 ... Which of these Subqueries work?

SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department.id);

B. SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);

SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);

D. SELECT department_id FROM employees WHERE SALARY > (SELECT AVG(salary) FROM employees GROUP BY department_id);

The given subqueries work because they both use the correct syntax and logic to retrieve the desired results. In the first subquery, it selects the distinct department_id from the employees table where the salary is greater than any average salary calculated for each department. This ensures that only the department_ids with salaries higher than the average are returned. The second subquery selects the department_id from the employees table where the salary is greater than the average salary calculated for each department. Again, this ensures that only the department_ids with salaries higher than the average are returned.

Explanation

The given subqueries work because they both use the correct syntax and logic to retrieve the desired results. In the first subquery, it selects the distinct department_id from the employees table where the salary is greater than any average salary calculated for each department. This ensures that only the department_ids with salaries higher than the average are returned. The second subquery selects the department_id from the employees table where the salary is greater than the average salary calculated for each department. Again, this ensures that only the department_ids with salaries higher than the average are returned.

Submit