1) What is RDBMS?
Relational Data Base Management Systems (RDBMS) that maintains data records and indices in tables. Relationships may be created and maintained across and among the
data and tables
2) What is SQL?
SQL stands for 'Structured Query Language', developed by IBM in 1970’s
3) What is SELECT statement?
The SELECT statement lets you select a set of values from a table in a database. The values selected from the database table would depend on the various conditions that are specified in the SQL query.
4) What is normalization?
Database normalization is a data design and organization process applied to data structures based on rules that help build relational databases.
5) What is the INSERT statement?
The INSERT statement allows us to insert information into a database.
6) How do you delete a record from a database?
Using the DELETE statement we can remove records or any particular column values from a database.
7) How to find the total number of records in a database table?
We can use the COUNT keyword for counting records,
SELECT COUNT(*) FROM Emp
8) What is View?
A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows.
9) What is Index?
An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently.
10) What is GROUP BY?
GROUP BY keyword has been added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called. Without the GROUP BY functionality, finding the sum for each individual group of column values was not possible.
11) What is the difference among "dropping a table", "truncating a table" and "deleting all records" from a table?
Dropping: (Table structure + Data are deleted), Invalidates the dependent objects, Drops the indexes
Truncating: (Data alone deleted), Performs an automatic commit, Faster than delete
Delete: (Data alone deleted), Doesn’t perform automatic commit
12) What's the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default.
Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.
13) What are triggers? How to invoke a trigger on demand?
Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.
Triggers can't be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined.
14) What is a join and explain different types of joins?
Join is used in queries to explain how different tables are related. Joins also let us select data from a table depending upon data from another table.
Types of joins:
Note: OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
15) What is a self join?
Self join is just like any other join, except that two instances of the same table will be joined in the query.
One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.
17) What are the Properties of Sub-Query?
A sub query must be enclosed in the parenthesis.
A sub query must be put in the right hand of the comparison operator, and
A sub query cannot contain a ORDER-BY clause.
A query can contain more than one sub-queries.
18) What are types of sub-queries?
Single-row sub query, where the sub query returns only one row.
Multiple-row sub query, where the sub query returns multiple rows,. and
Multiple column sub query, where the sub query returns multiple columns.
19) What are the different index configurations a table can have?
A table can have one of the following index configurations:
a) No indexes
b) A clustered index
c) A clustered index and many nonclustered indexes
d) A nonclustered index
e) Many nonclustered indexes
20) What is the difference between a Local and a Global temporary table?
A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.
A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.
21) What are the advantages and disadvantages of primary key?
Primary key Advantage:
1) It is a unique key on which all the other candidate keys are functionally dependent.
Primary key Disadvantage:
1) There can be more than one keys on which all the other attributes are dependent on.
22) What is a NULL value?
A NULL value takes up one byte of storage and indicates that a value is not present as opposed to a space or zero value.
23) Can Primary key is a Foreign Key on the same table?
Yes, Primary key is a Foreign Key on the same table.
24) What are the SQL Comparison Keywords?
There are other comparison keywords available in sql which are used to enhance the search capabilities of a sql query.
They are "IN", "BETWEEN...AND", "IS NULL", "LIKE".
25) What are the wildcards used for pattern matching?
Wildcards used for pattern matching are:
_ for single character substitution and % for multi-character substitution.
26) What are user defined data types and when you should go for them?
User defined data types lets you extend the base SQL Server data types by providing a descriptive name, and format to the database.
For example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should bevarchar(8). In this case you could create a user defined data type called Flight_num_type of varchar(8) and use it across all your tables.
27) What does Database Object means?
A database object in a relational database is a data structure used to either store or reference data. The most common object that most people interact with is the table. Other objects are indexes, stored procedures, sequences, views and many more.
When a database object is created, a new object type cannot be created because all the various object types created are restricted by the very nature, or source code, of the relational database model being used, such as Oracle, SQL Server or Access. What is being created is instances of the objects, such as a new table, an index on that table or a view on the same table.
28) What is Database Trigger?
A database trigger is stored code that is executed immediately after a predefined event. It is used to ensure the coordinated performance of related actions. Although implementation varies, all major relational databases support triggers.
29) What is SQL* Plus?
SQL* Plus is a command line tool proprietary to Oracle. You can send SQL queries to the server using the tool. It can also help you format the result of a query.
30) What is T-SQL?
T-SQL is the procedural programming language built into SQL Server RDBMS.
31) What is PL/SQL?
PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.
32) What is the difference between SQL and PL/SQL?
SQL is a query language that allows us to use a single query or execute a single insert/update/delete commands, SQL doesn’t support programming features like Conditional and Loop Statements.
PL-SQL is Oracle's "Programming Language" SQL, which allows us to write a full program (loops, conditions, variables, etc.) to accomplish multiple selects/inserts/updates/deletes.
33) What is the difference between SQL and SQL Server?
SQL is Structured Query Language used to communicate with databases, whereas SQL Server is Relational Database Management System from Microsoft?
34) What is the Difference between SQL and MySQL?
SQL is Structured Query Language used to communicate with databases, whereas MySQL is an Open Source Relational Database Management System like Oracle, SQL Server and Sybase etc…
35) What is Normalization?
Normalization is the process of efficiently organizing data in a database.
36) Why SQL knowledge is required for Software Testers?
SQL knowledge is required for Software Tester in order to Verify:
i) Data Integrity,
ii) Data consistency,
iii) Data Manipulations (Add/Edit/Delete)
iv) Data Back-up and Recovery,
v) Data Comparison,
37) What are the 3 important roles in Database are?
i) Database developers
ii) Database Testers
iii) Database Administrators
38) What is Data Integrity?
Data integrity refers to the overall completeness, accuracy and consistency of data.
39) What is Data back-up and Recovery?
40) What is Data Comparison?
Comparing Front-end data with Back-end Data,
Comparing one data resource with another (Ex: Database data with Excel file data)
Comparing columns from two different tables etc...
41) What are Advantages of SQL?
i) SQL Queries can be used to retrieve large amounts of records from a database quickly and efficiently.
ii) SQL is used to view the data without storing the data into the object.
iii) SQL joins two or more tables and show it as one object to user.
iv) SQL databases use long-established standard, which is being adopted by ANSI & ISO. Non-SQL databases do not adhere to any clear standard.
v) Using standard SQL it is easier to manage database systems without having to write substantial amount of code.
vi) SQL restricts the access of a table so that nobody can insert the rows into the table.
42) What are Disadvantages of SQL?
i) Interfacing an SQL database is more complex than adding a few lines of code.
ii) When table is dropped view becomes inactive. It depends on the table objects.
iii) Although SQL databases conform to ANSI & ISO standards, some databases go for proprietary extensions to standard SQL to ensure vendor lock-in.
iv) It is an object so it occupies space.
43) What is the SQL statement for extracting data from a database?
44) What is the SQL statement for updating data in a database?
45) What is the SQL statement for inserting new data in a database?
46) What is the SQL statement for returning only different values?
47) Which SQL keyword is used to sort the result-set?
48) What are the important SQL built-in Functions?
a) AVG() - Returns the average value
b) COUNT() - Returns the number of rows
c) FIRST() - Returns the first value
d) LAST() - Returns the last value
e) MAX() - Returns the largest value
f) MIN() - Returns the smallest value
g) SUM() - Returns the sum
h) UCASE() - Converts a value to upper case
i) LCASE() - Converts a value to lower case
j) MID() - Extract characters from a string or number
k) LEN() - Returns the length of a text field
l) ROUND() - Rounds a numeric field to the number of decimals specified
m) NOW() - Returns the current system date and time
n) FORMAT() - Formats how a field is to be displayed
49) What are the important clauses in SQL?
50) How can you return the number of records in a table using SQL?
SELECT COUNT(*) FROM TableName
51) What is ROWID?
ROWID is a pseudo column attached to each row of a table. It is 18 characters long, block no, row number are the components of ROWID.
52) What are defaults? Is there a column to which a default can't be bound?
A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and time stamp columns can't have defaults bound to them.
53) What is the advantage of foreign key in SQL?
It allows referencing another table using the primary key for the other table.
54) What is denormalization and difference between normalization?
Denormalization is the process of attempting to optimize the performance of a database by adding redundant data or by grouping data.
Normalization is the process of de-composing a relation with anomalies into a well structured relation.
55) What are constraints?
A constraint is basically a rule associated with a column that the data entered into that column must follow.
56) What is Nested Trigger?
A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.
57) What are the advantages and disadvantages of view?
Advantages of views:
1. View the data without storing the data into the object.
2. Restrict the view of a table i.e. can hide some of columns in the tables.
3. Join two or more tables and show it as one object to user.
4. Restrict the access of a table so that nobody can insert the rows into the table.
Disadvantages of Views:
1. Can not use DML operations on this.
2. When table is dropped view becomes inactive.. it depends on the table objects.
3. It is an object, so it occupies space.
58) What is a UNIQUE KEY?
A UNIQUE KEY is one or more columns that must be unique for each row of the table.
59) How can you call a PL/SQL procedure from SQL?
We can call by using of the EXECUTE (short form EXEC) command.
60) Can one select a random collection of rows from a table?
Yes. Using SAMPLE clause.
SELECT * FROM TableName SAMPLE(10);
10% of rows selected randomly will be returned.
61) Describe how NULLs work in SQL?
The NULL is how SQL handles missing values. Arithmetic operation with NULL in SQL will return a NULL.
62) What is the highest value that can be stored in a BYTE data field?
The highest value that can be stored in a BYTE field is 255 or from -128 to 127. Byte is a set of Bits that represent a single character. Usually there are 8 Bits in a Byte, sometimes more, depending on how the measurement is being made. Each Char requires one byte of memory and can have a value from 0 to 255 (or 0 to 11111111 in binary).
63) What is the main role of a primary key in a table?
The main role of a primary key in a data table is to maintain the internal integrity of a data table.
64) List all the possible values that can be stored in a BOOLEAN data field?
There are only two values that can be stored in a BOOLEAN data field: -1(true) and 0(false).
65) What is a SPOOL?
SPOOL command creates a print file of the report.
66) What is COMPUTE?
This command control computations on subsets created by the BREAK command.
67) What is a BREAK?
BREAK command clarify reports by suppressing repeated values, skipping lines & allowing for controlled break points.
68) What is a COLUMN?
COLUMN command defines column headings & format data values.
69) What is TTITLE & BTITLE?
TTITLE & BTITLE are commands to control report headings & footers.
70) What is Stored Procedure?
A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
e.g. sp_helpdb, sp_renamedb, sp_depends etc.
SQL Queries Examples