Below are the interview questions for SQL basic and intermediate level SQL Developers, Administrators, and Testers. These questions are about Commands, Operations, creating and editing database and tables.
1) How to fetch data from a Database Table?
Using SELECT Statement, we can fetch data from a Database Table
Syntax:
Or
SELECT * FROM table_name;
2) Explain about IN Operator?
The IN operator implements comparison to a list of values, that is, it tests whether a value matches any value in a list of values. IN comparisons have the following general format:
This comparison tests if value-1 matches value-2 or matches value-3, and so on. It is equivalent to the following logical predicate:
value-1 = value-2 [ OR value-1 = value-3 ] ...
3) Explain about FROM Clause in SQL?
The FROM clause always follows the SELECT clause. It lists the tables accessed by the query. For example,
When the From List contains multiple tables, commas separate the table names. For example,
SELECT sp.*, city FROM sp, s WHERE sp.sno=s.sno
When the From List has multiple tables, they must be joined together.
4) What is the parameter substitution symbol used with INSERT INTO command?
5) What are the various uses of database triggers?
Database triggers can be used to enforce business rules, to maintain derived values and perform value-based auditing.
6) What is a event handler in sql?
7) What are two methods of retrieving SQL?
The two methods of retrieving SQL are
1-Select
2-using Cursor.
8) What is a synonym? How is it used?
A synonym is used to reference a table or view by another name. The other name can then be written in the application code pointing to test tables in the development stage and to production entities when the code is migrated. The synonym is linked to the AUTHID that created it.
9) What is referential integrity?
Referential integrity refers to the consistency that must be maintained between primary and foreign keys, i.e. every foreign key value must have a corresponding primary key value.
10) Explain the EXPLAIN statement?
The explain statement provides information about the optimizer's choice of access path of the SQL.
11) How is the SUBSTR keyword used in SQL?
SUBSTR is used for string manipulation with column name, first position and string length used as arguments. E.g. SUBSTR (NAME, 1 3) refers to the first three characters in the column NAME.
12) What is the difference between group by and order by?
Group by controls the presentation of the rows, order by controls the presentation of the columns for the results of the SELECT statement.
13) What is a subselect? Is it different from a nested select?
A subselect is a select which works in conjunction with another select. A nested select is a kind of subselect where the inner select passes to the where criteria for the outer select.
14) What is the use of CASCADE CONSTRAINTS?
When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.
15) How do you prevent output from coming to the screen?
The SET option TERMOUT controls output to the screen. Setting TERMOUT OFF turns off screen output. This option can be shortened to TERM.
16) Can Primary key is a Foreign Key on the same table?
Yes, Primary key is a Foreign Key on the same table.
17) How do you execute a host operating system command from within SQL?
By use of the exclamation point “!” (in UNIX and some other OS) or the HOST (HO) command.
18) What is a Cartesian product?
A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join.
19) How can variables be passed to a SQL routine?
By use of the & symbol. For passing in variables the numbers 1-8 can be used (&1, &2,...,&8) to pass the values after the command into the SQL PLUS session. To be prompted for a specific variable, place the ampersanded variable in the code itself: “select * from dba_tables where owner=&owner_name;” . Use of double ampersands tells SQLPLUS to resubstitute the value for each subsequent use of the variable, a single ampersand will cause a reprompt for the value unless an ACCEPT statement is used to get the value from the user.
20) What command is used to get back the privileges offered by the GRANT command?
Revoke command is used to get back the privileges offered by the GRANT command.
21) What are the advantages of procedures?
Advantages of procedures:
- Loaded once and used many times.
- Performance better coz all SQL statements are sent in one go from the application to the database.
- Security (no object privileges are given directly).
- Invoker's rights possible.
- Data integrity, productivity.
22) What is Parsing?
Parsing checks syntax, checks privileges, and allocating Private SQL Area.
23) What is Cursor?
Name or handle to a private SQL area where Oracle parses and fetches query results.
24) Is SQL supports Conditional and Loop Statements?
No Basically SQL is a Command based Language, not a procedural language, but it has Operators and built-in Functions.
0 Comment to "SQL interview questions on Commands, Operations, editing database and tables"
Post a Comment