12/14/12

SQL Statements Questions and Answers

,
Questions and Answers on SQL Statements

1. What is meaning for SQL statements?

SQL statements are like questions or commands you wish to act on a database.  A statement written in SQL that is issued to a database is to retrieve data or to create, insert, update, or delete data in the database.

2. What are different types of SQL statements?

The different SQL statements are as follows:

1. Data Definition Language (DDL) Statements.
2. Data Manipulation Language (DML) Statements.
3. Data Control Language (DCL) Statements.
4. Transaction control language (TCL) statements.


3. What is DDL?
DDL stands for Data Definition Language. DDL statements are used to build and modify the structure of your tables and other objects in the database. When you execute a DDL statement, it takes effect immediately.

4. What are the different DDL statements?

The most basic statements of DDL are as follows:
CREATE,
ALTER,
RENAME,
DROP,
and TRUNCATE.

5. What is the purpose of CREATE Table Statement?

You can create a table by using the SQL CREATE table statement. The CREATE TABLE statement allows you to create a table, define the physical attributes of the columns in the table, and define constraints to restrict the values that are allowed in the table.
When creating a table, you need to understand the concepts of null value and default value. A null value indicates the absence of a column value for a row. It is not the same as a value of zero or all blanks. It means ″unknown." It is not equal to any value, not even to other null values. If a column does not allow the null value, a value must be assigned to the column, either a default value or a user supplied value.

6. What is the syntax for Create Table?

CREATE TABLE
( field1 datatype ( NOT NULL ),
  field2 datatype ( NOT NULL ) );

7.  Example for Create table statement?

SQL> CREATE TABLE Student
     (Name VARCHAR2(25),
       StudentNumber NUMBER(5) NOT NULL,
       Class NUMBER(5),
       Major VARCHAR2(5),
       Primary key (StudentNumber)
     );

Table created.

8. What is the purpose of Alter Table?

We can alter the definition of a table to add a column, to add an integrity constraint, to redefine a column (data type, size, default value), to modify storage characteristics or other parameters, to enable, disable, or drop an integrity constraint or trigger, to explicitly allocate an extent, to allow or disallow writing to a table, to modify the degree of parallelism for a table.

9. What is the Syntax for Alter Table?

ALTER TABLE ADD attr data type;
10. Example to Alter the Table?

SQL> ALTER TABLE Student ADD (advisor VARCHAR2(30));

11. What is the syntax to add column to table using Alter?

SQL> Alter table tablename add columnname columndefinition;

Ex: Alter table student add RNo char[10];

12. What is the syntax to modify the column using Alter?

SQL> Alter table tablename modify columnname columndefinition;

Ex: Alter table student modify RNo char[20];

13. What is the syntax to drop the column using Alter?

SQL> Alter table tablename dropcolumn columnname;

Ex: Alter table tablename dropcolumn LastName;

14. What is the purpose of Drop Table?

Drop Table statement is used to remove a table and all its data from the database.

15. What are the two options to drop a table?

To Drop a table we use Cascade and Restrict.
Cascade specifies that any foreign key constraint violations that are caused by dropping the table will cause the corresponding rows of the related table to be deleted.
Restrict blocks the deletion of the table of any foreign key constraint violations would be created.

16. What is the syntax for DROP table statement?

DROP TABLE [ RESTRICT|CASCADE ];

17. Example to DROP a Table?

SQL> DROP TABLE Student;

Table dropped.

18. What is the purpose of RENAME statement?

Use the RENAME statement to rename a table, view, sequence, or private synonym for a table, view, or sequence.

19. What is the Syntax for RENAME?

SQL> Oldname as Newname;

20. Example to RENAME the table?

To rename Emp table to Employee you can give the following command.

SQL> Rename Emp as Employee;

21. What is the purpose of TRUNCATE statement?

Use the Truncate statement to delete all the rows from table permanently . It is same as “DELETE FROM ” except
•    Truncate does not generate any rollback data hence, it cannot be roll backed.
•    If any delete triggers are defined on the table. Then the triggers are not fired
•    It deallocates free extents from the table. So that the free space can be use by other tables.

22. What is the syntax for TRUNCATE?

SQL> Truncate table ;
23. Example to TRUNCATE the table?

SQL> Truncate table Emp;

24. What is the purpose of DML statements?

Data Manipulation Language (DML) statements are used for managing data within schema objects. DML statements lets users insert , modify, and delete  the data in the database.

25. What the different types of DML commands?

SQL provides SELECT, INSERT, DELETE, UPDATE commands.

26. What is the purpose of SELECT Command?

SELECT is used to select particular column or entire table.

27. What is syntax for SELECT command? Give example?

SQL> Select columnname from tablename;
Or
Select * from tablename;

Ex: Select firstname , lastname from student;
Select * from student;

28. What is the purpose of INSERT command?

INSERT command is used to insert values into table.

29. What is the syntax for INSERT command?

SQL> Insert into tablename values (value1…..value n);
Insert into tablename values($value 1… $value n);

Ex:
Insert into student values (‘chitu’,’ramu’,’Bnagar’,’hyd’);
Insert into student values (‘$firstname’,’$lastname’,’$address’,’$city’);

30. What is the purpose of UPDATE command?
UPDATE command is used to change or modify the existing column.

31. What is the syntax for UPDATE command and give an example?

SQL> Update tablename set columnname=new value where   columnname=somevalue;

Ex: Update student set address =’Bnagar’ where firstname=’chitu’;

32. What is the purpose of DELETE command?

DELETE command is used to delete the corresponding row.

33. What is the syntax for DELETE command and an example?

SQL> Delete from tablename where columnname=somevalue;

Ex: Delete from student where firstname=’chitu’;

34. What is the purpose of DCL commands?

Data Control Language commands are used   to control access to data in a database.

35. What are the DCL commands?

The DCL commands are Grant and Revoke.
GRANT - gives user's access privileges to database.
REVOKE - withdraw access privileges given with the GRANT command.

36. What is the purpose of TCL statements?

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

37. What are transaction control statements?

Transaction control statements are COMMIT, SAVEPOINT, ROLLBACK, and SET TRANSACTION.

38. What is the purpose of SAVEPOINT statement?

SAVEPOINT is used to identify a point in a transaction to which you can later roll back.

39. What is the purpose of SET TRANSACTION statement?

SET TRANSACTION statement is used to change the transaction options like isolation level and what rollback segment to use.

40. What is the purpose of COMMIT command?

COMMIT command is used to save all transactions.

Syntax: commit;

41. What is the purpose of ROLLBACK command?

ROLLBACK command is used to undo the transaction. ROLLBACK - restore database to original since the last COMMIT
Syntax: Rollback;

42. What are the three clauses of SELECT statement?

A basic SELECT statement includes 3 clauses:  They are SELECT, FROM, WHERE.
 SELECT FROM WHERE
SELECT: Specifies the attributes that are part of the resulting relation.
FROM: Specifies the tables that serve as the input to the statement.
WHERE: Specifies the selection condition, including the join condition.

43. How to get unique rows in SELECT statement?

We can get unique rows by typing the keyword DISTINCT after SELECT.
Example: Select DISTINCT * FROM …WHERE …;

0 comments to “SQL Statements Questions and Answers ”

Post a Comment