Oracle SQL Syllabus
DDL (Data Definition Language)
• Create
• Alter
• add
• modify
• rename
• drop
• Drop
• truncate
DML (Data Manipulation Language)
• INSERT
• UPDATE
• DELETE
• MERGE
• Function
• Max()
• Min()
• length()
• avg()
• count()
• substr()
• sysdate()
• instr()
• trim()
Nvl Function
• CONSTRAINTS
• Column Level Constraint
• Table Level Constraints
• CASCADING CONSTRAINTS
• NOT NULL Constraint
• UNIQUE Constraint
• PRIMARY KEY Constraint
• CHECK Constraint
• FOREIGN KEY Constraint
• ALTER TABLE with CONSTRAINT
• Enabling Constraint
• Disabling Constraint
• Group Function
• Using NVL with group function
• HAVING with Group Function
Subqueries
• Single Row Subquery
• Multiple Row Subquery
• Group function using subquery
• ALL with subquery
• NOT IN with subquery
• Join (Display Data from Multiple Tables)
• String Handling
• INNERJOIN
• LEFTOUTERJOIN
• RIGHTOUTERJOIN
• EQUIJOIN
• NON-EQUIJOIN
• OUTER Join
• SELF Join
• Merge
• Substitution Variables
• Using VERIFY command
• Using SET commands
• Using Format commands
• BREAK Command
• Creating VIEW
• SIMPLE VIEW
• WHERE using View
• DML operation using View
• FORCE using View
• Column Alias using View
• GROUP FUNCTION using VIEW
• WITH CHECK OPTION using VIEW
• Inline View
• Creating Sequence, Index, Synonym
• How to use SequenceINDEX
• UNIQUE INDEX
• Function Based Indexes
• Using SET Operator
• UNION
• UNION ALL
• INTERSECT
• MINUS
• DISTINCT
• Advanced Subqueries
• Multiple Column Subqueries
• Pairwise Comparison Subquery
• Using a Subquery in FROM clause
• Using EXISTS operator INSERT
• Using EXISTS operator UPDATE
• Correlated UPDATE and DELETE
• Using WITH clause
• Enhancement to the GROUP BY clause
• GROUPING using GROUP BY
• GROUPING SETS using GROUP BY
• Controlling User Access
• CREATE TABLESPACE
• Privileges in Oracle
• CREATE USER
• GRANTS
• ROLE
• Change Password
• Object Privileges
• WITH GRANT OPTION Keyword
• PUBLIC Keyword
• REVOKE Object Privileges
• Many more..
SQL Interview Questions
1) What is SQL?
SQL stands for structured query language. It is a database language used for database creation, deletion, fetching rows and modifying rows etc. sometimes it is pronounced as se-qwell.
2) When SQL appeared?
:-It appeared in 1974.
3) What are the usages of SQL?
:-To execute queries against a database
To retrieve data from a database
To inserts records in a database
To updates records in a database
To delete records from a database
To create new databases
To create new tables in a database
To create views in a database
4) Does SQL support programming?
:-No, SQL doesn't have loop or Conditional statement. It is used like commanding language to access databases.
5) What are the subsets of SQL?
:-
Data definition language (DDL)
Data manipulation language (DML)
Data control language (DCL)
6) What is data definition language?
Data definition language(DDL) allows you to CREATE, ALTER and DELETE database objects such as schema,tables, view, sequence etc.
7) What is data manipulation language?
Data manipulation language makes user able to access and manipulate data. It is used to perform following operations.
Insert data into database
Retrieve data from the database
Update data in the database
Delete data from the database
8) What is data control language?
Data control language allows you to control access to the database. It includes two commands GRANT and
REVOKE.
GRANT: to grant specific user to perform specific task.
REVOKE: to cancel previously denied or granted permissions.
9) What are the type of operators available in SQL?
Arithmetic operators
Logical operators
Comparison operator
10) What is the difference between clustered and non clustered index in SQL?
There are mainly two type of indexes in SQL, Clustered index and non clustered index. The differences between these two indexes is very important from SQL performance perspective.
1) One table can have only one clustered index but it can have many non clustered index. (approximately 250).
2) clustered index determines how data is stored physically in table. Actually clustered index stores data in cluster, related data is stored
together so it makes simple to retrieve data.
3) reading from a clustered index is much faster than reading from non clustered index from the same table.
4) clustered index sort and store data rows in the table or view based on their key value, while non cluster have a structure separate from
the data row.
Sample Question
2. What is the difference between SQL and PL/SQL?
3. What are various DDL commands in SQL? Give brief description of their purposes.
4. What are various DML commands in SQL? Give brief description of their purposes.
5. What are various DCL commands in SQL? Give brief description of their purposes.
6. Can you sort a column using a column alias?
7. Is a NULL value same as zero or a blank space? If not then what is the difference?
8. Say True or False. Give explanation if False.
9. If a column value taking part in an arithmetic expression is NULL, then the result obtained would be NULL.
10.Which function returns the remainder in a division operation?
