What Is SQL?
SQL
(pronounced as the letters S-Q-L or as sequel) is an abbreviation for
Structured Query Language. SQL is a language designed specifically for
communicating with databases. SQL is designed to do one thing and do it
well—provide you with a simple and efficient way to read and write data from a
database.
What is the difference between database & relational database.
Database or
Database Management System(DBMS) and Relational Database Management
System(DBMS) are both used by SQL to store data and structures. However, each
type of Database Management System is preferred with respect to different uses.
The main difference
between the two is that DBMS saves your information as files whereas RDMS saves
your information in tabular form. Also, as the keyword Relational implies, RDMS
allows different tables to have relationships with one another using Primary
Keys, Foreign Keys etc. This creates a dynamic chain of hierarchy between
tables which also offers helpful restriction on the tables.
DBMS sorts out its
tables through a hierarchal manner or navigational manner. This is useful when
it comes to storing data in tables that are independent of one another and you
don’t wish to change other tables while a table is being filled or edited.
What are different categories of SQL commands?
SQL command
falls into following four categories:
- DML (Data
Manipulation Language) which provides data manipulation features
- DDL (Data
Definition Language) which is used to manipulate database structures
- TCL (Transaction
Control Language) that takes in charge data transaction verification and
error handling
- DCL (Data Control
Language) are security statements that feature user restrictions and data
access permissions to promote security of your data.
What is the Basic Structure of an SQL?
SQL is
framed upon the structure of relational operations. It is based on certain
modifications and enhancements.
A very basic
SQL query form is: select A1, A2, ..., An from R1, R2, ..., Rm where P;
Here An are
attributes, Rm is the relations within the database and P is the predicate or
filter.
Define SELECT, INSERT, CREATE, DELETE, UPDATE, DROP keywords
SELECT
keyword is used to highlight and get entries in rows from tables or views. It
can also be accompanied by AS keyword to provide an alias. To filter the SELECT
statement, WHERE clause may be included to provide filter conditions and select
only the wished entries that satisfy the condition.
INSERT allows
to add or insert a row or multiple rows in a database table. Accompanied by VALUES
keyword lets you add a row with specific values. INSERT may also be accompanied
with SELECT to insert the preselected row.
CREATE is a keyword
used to create elements in SQL. It is usually accompanied with the keyword to
be created such as CREATE DATABASE, CREATE TABLE, CREATE VIEW, etc.
DELETE keyword is
used to deletes record(s) in a database. You should always use it carefully to
avoid unwanted data loss. You may delete records you didn’t want to delete. Use
WHERE clause to specify the range of the records you wish to delete.
UPDATE keyword
updates or changes the existing data within an existing record. Be sure to note
that the record must be existent.
DROP keyword
drops or deletes a table within the database.
Which command displays the SQL command in the SQL buffer, and then
executes it?
RUN
What are the key differences between SQL and P/L SQL?
SQL or Structured Query Language is a language which is used to
communicate with a relational database. It provides a way to manipulate and
create databases. On the other hand, PL/SQL is a dialect of SQL which is used
to enhance the capabilities of SQL. It was developed by Oracle Corporation in
the early ’90s. It adds procedural features of programming languages in SQL.
What is data definition language?
DDL or Data Definition Language pertains to the SQL commands
directly affecting the database structure. DDL is a category of SQL command
classifications that also include DML (Data Manipulation Language),
Transactions, and Security. A particular attribute of DDL commands is
statements that can manipulate indexes, objects, tables, views, triggers, etc. Three popular DDL keywords in SQL are:
CREATE – which is
used to create a table
CREATE TABLE
tableName (name data_type);
ALTER – used to
modify entries or existing columns within a table.
ALTER TABLE
tableName [additional syntax such as ADD, DROP, MODIFY]
DROP – used to
Delete or Drop an existing table along with its entries, constraints, triggers,
indexes, and permissions. Essentially deletes the table.
DROP TABLE
tableName;
What is Data Manipulation Language?
DML or Data
Manipulation Language is a set of commands that are classified pertaining to
its capability to give users permission to change entries within the database.
This may be through Inserting, Retrieving, Deleting or Updating data within
tables. Popular DML statements arise from these core functionalities and are
listed below:
SELECT – used to
highlight a row within a table and retrieve it.
SELECT
[columnName] FROM [tableName]
UPDATE – used
to update entries from existing tables.
UPDATE
[tableName] SET [value]
INSERT – used to
insert entries into an existing table.
INSERT INTO
[tableName]
DELETE – used
to delete entries from an existing table
DELETE FROM
[tableName]
What is Transaction Control Language (TCL)?
TCL is a category of SQL commands which primarily deals with the
database transaction and save points. These keywords implement the SQL
functions and logic defined by the developer into the database structure and
behavior. Examples of these TCL commands are:
COMMIT – used to
commit a transaction
ROLLBACK – in any
advent of errors, transaction rollback is invoked by this keyword.
SAVEPOINT – keyword
representing the reverting point of rollback
SET
TRANSACTION – sets the specifics of the transaction
What is Data Control Language (DCL)?
Data Control Language or DCL oversees the issuance of access and
restrictions to users, including the rights and permissions required within the
SQL statements. Example DCL keywords are:
GRANT – DCL
keyword that provides access to certain databases to users.
REVOKE –
opposite of the GRANT keyword. Revokes or withdraws the privileges given to the
user.
Define tables and fields in a database
In terms of databases, a table is referred to as an arrangement of
organized entries. It is further divided into cells which contain different
fields of the table row.
A field pertains to a
data structure that represents a single piece of entry. They are then further
organized to records. They practically hold a single piece of data. They are
the basic unit of memory allocation for data and is accessible.
What are different types of keys in SQL?
Keys are a
vital feature in RDMS, they are essentially fields that link one table to
another and promote fast data retrieval and logging through managing column
indexes.
Different
types of keys are:
Primary Key – a
unique key that identifies records in database tables. By unique it means that
it must not be Null and must be unique in the table.
Candidate
Key – a unique field which identifies for column or group of columns
independently, without any required reference to other fields.
Alternate
Key – can be substituted in use for Primary Keys but are
considered as a secondary. The difference is that Alternate Keys can have a
Null value, provided that the columns have data within them. A type of
Candidate Key which is also required to be unique.
Unique Key – Keys
that offer restriction to prevent duplicate data within rows except for null
entries.
The other
keys available are Foreign Keys, Super Keys, and Composite Keys.
Name the different types of indexes in SQL and define them.
Unique Index: Prevents duplicate entries within uniquely indexed
columns. They are automatically generated if a Primary Key is available.
Clustered Index: Used
to organize or edit the arrangement within the table, with respect to the key
value. Each table is only allowed to have a single clustered index only.
NonClustered Index: Conversely,
NonClustered Index only manages the order of logic within entries. It does not
manage the arrangement and tables can have multiple NonClustered Indexes.
How can you call a PL/SQL procedure from SQL?
By use of the EXECUTE (short form EXEC)
command.
Which is the subset of SQL commands used to manipulate Oracle
Database structures?
Data Definition Language (DDL)
What is UNION and UNION ALL keyword in SQL and what are their
differences?
The UNION operator in SQL
combines multiple sets highlighted in the SELECT statements. The restrictions
of the set are: (1) column number must be identical, (2) Data Types in the set
must be the same, and (3) the order of the column highlighted in the SELECT
statement must be the same. It automatically eliminates duplicate rows within
the results highlighted in the SELECT statement.
UNION ALL does the same
function as the UNION, but it includes all, including the duplicate rows.
SELECT C1, C2 FROM T1
UNION
SELECT Cx, Cy FROM T2;
What are the different types of joins in SQL?
The join keyword queries
entries from multiple tables. It is used with different keys to find these
entries and is conscious on the link between fields.
Inner Join: Returns rows which are common between the tables
Right Join: Returns rows of the right-hand side table, including the common
rows.
Left Join: Returns rows of the left-hand side table, including the common
rows.
Full Join: Returns all rows, regardless if common or not.
Write a query to select the second highest salary from a table.
SELECT max(salary) AS salary_2 FROM emp WHERE salary
< (SELECT max(salary) AS salary_1 FROM emp)
Write a query to select the 5th highest salary from a table.
SELECT min(salary) AS high5
FROM employee
WHERE salary IN(SELECT DISTINCT TOP 5 salary FROM employee ORDER BY salary DESC)
How to find duplicate records with the number they are duplicated?
SELECT Id, count (*) as num_records
from table
group by id
having count (*) > 1
What is the difference among UNION, MINUS and INTERSECT?
The UNION keyword is used in
SQL for combining multiple SELECT queries but deletes duplicates from the
result set.
The INTERSECT keyword is only used for retrieving common rows
using SELECT queries between multiple tables.
The MINUS keyword essentially subtracts between two SELECT
queries. The result is the difference between the first query and the second
query. Any row common across both the result set is removed from the final
output.
What command is used to get back the privileges offered by the
GRANT command?
REVOKE
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.
What command is used to create a table by copying the
structure of another table?
CREATE TABLE .. AS SELECT command
What is the difference between Delete and Truncate command in
SQL?
Delete command and
truncate command both will delete the data, however the truncate command can
not be rolled back as delete can be. TRUNCATE is a DDL command whereas DELETE
is a DML command. The delete command can be used for selected records using the
where clause but with the truncate command we have to loose data. DELETE
statement is a logged operation and hence takes more time then truncate.
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 SQLPLUS
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.
What SQLPlus command is used to format output from a select?
This is best done with the COLUMN command.
Can Primary key is a Foreign Key on the same table?
Yes
You want to determine the location of identical rows in a table
before attempting to place a unique index on the table, how can this be
done?
Oracle tables always have one guaranteed unique column, the rowid
column. If you use a min/max function against your rowid and then select
against the proposed primary key you can squeeze out the rowids of the
duplicate rows pretty quick. For example:
select rowid from emp e
where e.rowid > (select min(x.rowid)
from emp x
where x.emp_no = e.emp_no);
What is the use of the DROP option in the ALTER TABLE
command?
It is used to drop constraints specified on the table.
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.
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.
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.
Which function is used to find the largest integer less than or
equal to a specific value?
FLOOR
What is a PRIMARY KEY?
The PRIMARY KEY is the column(s) used to uniquely identify each
row of a table.
What is a FOREIGN KEY?
A FOREIGN KEY is one or
more columns whose values are based on the PRIMARY or CANDITATE KEY values from
the database.
What is a UNIQUE KEY?
A UNIQUE KEY is one or more columns that must be unique for each row
of the table.
What is the difference between UNIQUE and PRIMARY KEY?
The UNIQUE KEY column restricts entry of duplicate values but
entry of NULL value is allowed. In case of PRIMARY KEY columns entry of
duplicate as well as <null> value is also restricted.
Consider the below DEPT and EMPLOYEE table and answer the below
queries.
DEPT DEPTNO (NOT NULL , NUMBER(2)), DNAME (VARCHAR2(14)), LOC
(VARCHAR2(13)
EMPLOYEE EMPNO (NOT NULL , NUMBER(4)), ENAME (VARCHAR2(10)), JOB
(VARCHAR2(9)), MGR (NUMBER(4)), HIREDATE (DATE), SAL (NUMBER(7,2)), COMM
(NUMBER(7,2)), DEPTNO (NUMBER(2)) MGR is the EMPno of the Employee whom the
Employee reports to. DEPTNO is a foreign key.
List all the Employees who have at least one person reporting to
them.
SELECT ENAME FROM EMPLOYEE
WHERE EMPNO IN (SELECT MGR FROM EMPLOYEE);
List the highest salary paid for each job.
SELECT JOB, MAX(SAL)
FROM EMPLOYEE GROUP BY JOB
In which year did most people join the company? Display the year
and the number of Employees.
SELECT TO_CHAR(HIREDATE,'YYYY') "YEAR", COUNT(EMPNO)
"NO. OF EMPLOYEES"
FROM EMPLOYEE
GROUP BY TO_CHAR(HIREDATE,'YYYY')
HAVING COUNT(EMPNO) = (SELECT MAX(COUNT(EMPNO))
FROM EMPLOYEE
GROUP BY TO_CHAR(HIREDATE,'YYYY'));
Write a correlated sub-query to list out the Employees who earn
more than the average salary of their department.
SELECT ENAME,SAL
FROM EMPLOYEE E
WHERE SAL > (SELECT AVG(SAL)
FROM EMPLOYEE F
WHERE E.DEPTNO = F.DEPTNO);
Find the nth maximum salary.
SELECT ENAME, SAL
FROM EMPLOYEE A
WHERE &N = (SELECT COUNT (DISTINCT(SAL))
FROM EMPLOYEE B
WHERE A.SAL<=B.SAL);
Select the duplicate records (Records, which are inserted, that
already exist) in the EMPLOYEE table.
SELECT * FROM EMPLOYEE A
WHERE A.EMPNO IN (SELECT
EMPNO
FROM
EMPLOYEE
GROUP BY
EMPNO
HAVING COUNT(EMPNO)>1)
AND A.ROWID!=MIN (ROWID));
Write a query to list the length of service of the Employees (of
the form n years and m months).
SELECT ENAME
"EMPLOYEE",TO_CHAR(TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12))
||' YEARS '|| TO_CHAR(TRUNC(MOD(MONTHS_BETWEEN
(SYSDATE, HIREDATE),12)))||' MONTHS ' "LENGTH OF SERVICE"
FROM EMPLOYEE;