What is RDBMS?
RDBMS stands for Relational Database Management System defined by E.F.Codd. Data is stored in the form of rows and columns. Application interact with the Database via DBMS using Structure Query Language(SQL).
What is DML and DDL?
DML and DDL are subsets of SQL.
- DML – Data Manipulation Language
- DDL – Data Definition Language.
What is COMMIT option?
Saving all changes Created by DML statements
What are DML some commands?
SELECT: retrieve data from database
INSERT: insert data into table
UPDATE: updates existing data within table
DELETE: deletes all records from table
EXPLAIN PLAN: explain access path to data
LOCK TABLE: control concurrency
What are DDL some commands?
CREATE: create objects in database
ALTER: alters structure of database
DROP: delete objects from database
TRUNCATE: remove all records from a table, including all spaces allocated for the records are removed
COMMENT: add comments to data dictionary
RENAME: rename an object
What is DCL?
Data Control Language statements.
GRANT: gives user’s access privileges to database
REVOKE: withdraw access privileges given with the GRANT command
What is TCL?
Transaction Control statements manage the changes made by DML statements. Groups statements together into logical transactions.
COMMIT: save work done
SAVEPOINT: identify a point in a transaction where you can later roll back
ROLLBACK: restore database to original since the last COMMIT
SET TRANSACTION: Change transaction options like isolation level and what rollback segment to use
Difference between TRUNCATE, DELETE and DROP commands?
DELETE: used to remove some or all rows from a table.
TRUNCATE: removes ALL rows from a table. Cannot be rolled back
DROP: removes a table from the database. All the tables’ rows, indexes and privileges will also be removed.
What is a primary key?
The column (columns) that has completely unique data throughout the table is known as the primary key field. Primary Key cannot have NULL values. Primary Key cannot have duplicate values.
What are foreign keys?
Foreign key field is a field that links one table to another table’s primary or foreign key. It allows NULL values. It allows Duplicate values.
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.
Can a table have more than one foreign key defined?
A table can have any number of foreign keys defined. It can have only one primary key defined.
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).
Describe how NULLs work in SQL?
Its how SQL handles missing values. Arithmetic operation with NULL in SQL will return a NULL.
What is a join?
Join is a process of retrieve pieces of data from different sets (tables) and returns them to the user or program as one “joined” collection of data.
How do you add record to a table?
INSERT into table_name VALUES (‘val1′,’val2′,’val3”val4’);
How do you add a column to a table?
ALTER TABLE Department ADD (AGE NUMBER);
How do you change value of the field?
update EMP_table set number = 200 where item_munber = ‘CD’;
update EMP_table set ename = ‘TABLE’ where phone = ‘1234567890’;
What is Normalization?
Process of table design is called normalization.
What is use of Group by clause?
Group by clause is used to display data by combining or grouping one or more columns. (GROUP BY clause group and summarize query results). Basically Group By clause comes after Where Clause and before Having Clause.
What is Having Clause?
Having clause is used to filter the column generated by aggregate functions. As where clause cannot be used with aggregate functions.