SQL Aptitude Question 1
You can add a row using SQL in a database with which of the following?
- ADD
- CREATE
- INSERT
- MAKE
Answer: C. INSERT
Solution
In implicit insertion, particular field values are inserted:
The syntax is as follows:
insert into <tablename>(list of columns) values(list of values);
In explicit insertion, all field values are inserted explicitly:
The syntax is as follows:
insert into <tablename>values(list of values);
For example:
INSERT INTO student(stu_id,name,total) values(101,'BYJU’S',120);
SQL Aptitude Question 2
The command to remove rows from a table 'CUSTOMER' is:
- REMOVE FROM CUSTOMER ...
- DROP FROM CUSTOMER ...
- DELETE FROM CUSTOMER WHERE ...
- UPDATE FROM CUSTOMER ...
Answer: C. DELETE FROM CUSTOMER WHERE ...
Solution
The delete command is used to remove records from a table.
The syntax of the delete command is as follows:
Delete from <tablename>;
If you want to remove a particular record, then we use the where clause.
The syntax of the delete command is as follows:
Delete from <tablename> where <condition>;
Truncate — When we truncate a table, the data inside the table is erased, but the table's structure stays unchanged.
SQL Aptitude Question 3
The HAVING clause does which of the following?
- Acts like a WHERE clause but are used for groups rather than rows.
- Acts like a WHERE clause but is used for rows rather than columns.
- Acts like a WHERE clause but is used for columns rather than groups.
- Acts EXACTLY like a WHERE clause.
Answer: A. Acts like a WHERE clause but are used for groups rather than rows.
Solution
A form of restriction statement is "where." To restrict all the data from the database, use the where clause. Before retrieving results, where clause is used. However, the having clause is used after data retrieval. A type of filtering command is the having clause.
An example of having clause is as follows:
SELECT COUNT(CustomerID), Country. FROM Users GROUP BY Country HAVING COUNT(CustomerID) > 5;
SQL Aptitude Question 4
To remove duplicate rows from the results of an SQL SELECT statement, the ________ qualifier specified must be included.
- ONLY
- UNIQUE
- DISTINCT
- SINGLE
Answer: C. DISTINCT
Solution
"If you want Oracle to only return one copy of each set of duplicate records selected, use DISTINCT or UNIQUE" (these two keywords are synonymous). Rows that match the values of each expression in the select list are considered duplicate rows.
As a result, there shouldn't be any distinction between the two (in terms of performance or else) since Oracle treats both equally.
Additionally, avoid using UNIQUE in SELECT statements for other databases (MySQL, PostgreSQL, etc.). Because ANSI standards use DISTINCT with SELECT statements, you raise with a mistake. The use of UNIQUE with SELECT statements is mainly a product-specific holdover from earlier Oracle DBs developed prior to the creation of the ANSI standard.
SQL Aptitude Question 5
Find the SQL statement below that is equal to the following: SELECT NAME FROM CUSTOMER WHERE STATE = 'VA';
- SELECT NAME IN CUSTOMER WHERE STATE IN ('VA');
- SELECT NAME IN CUSTOMER WHERE STATE = 'VA';
- SELECT NAME IN CUSTOMER WHERE STATE = 'V';
- SELECT NAME FROM CUSTOMER WHERE STATE IN ('VA');
Answer: D. SELECT NAME FROM CUSTOMER WHERE STATE IN ('VA');
Solution
Multiple OR conditions can be avoided by using the IN function.
The IN function has the following syntax:
SELECT columns FROM tables WHERE column1 in (value1, value2, .... value_n);
This SQL query will return the records whose column 1 contains the values 1, value 2,..., or value n. Any valid SQL select, insert, update, or delete statement is compatible with the IN function.
For example:
A SQL statement by using the IN function is as follows:
SELECT * FROM company WHERE company_name IN ( 'IBM', 'Hewlett Packard', 'Microsoft');
This would return all rows with the company name IBM, Hewlett Packard, or Microsoft. Due to the usage of the * in the select, the result set would include all fields from the company database.
Similarly, for SELECT NAME FROM CUSTOMER WHERE STATE = 'VA'; the SQL SELECT NAME FROM CUSTOMER WHERE STATE IN ('VA'); is the same.
☛ Related Questions:
Comments
write a comment