PLUS TWO COMPUTER APPLICATION
(+2 Computer Application / Computer Science ,
Previous Questions Chapter wise ..
Chapter 9. Structured Query Language
1. ..…. keyword is used to in SELECT query to eliminate duplicate values in a column.
(a) UNIQUE
(b) DISTNICT
(c) NOT NULL
(d) PRIMARY
Ans.b
2. How will you add a new column to an existing table using SQL Statement?
If we want to add a column at the first position, use the clause FIRST. If we want to add a column at a specified position, use the clause ALTER <column _name>.
Note that if we do not specify the position of the new column, then by default, it
will be added as the last column of the table. Remember that the newly added
column contains only NULL values.
syntax: ALTER TABLE <table name>
ADD <column name> <datatype >[<size>] [<constraint>]
[FIRST | AFTER <column name>];
3. .Explain the SQL statements used to insert and delete data from a table.
The DML command INSERT INTO is used to insert tuples into tables.
The syntax is:
INSERT INTO <TABLE_NAME> [<column1><column2>,,...<columnN>,]
VALUES(<value1><value2>,,..<valueN);
Here <table_name> is the name of the table into which the tuples are to be
inserted;<column1>,<column2> , , …<columnN> indicate the name of columns
in the table into which values are to be inserted;<value1>,<value2> , …<valueN> are the values that are inserted into the columns specified in the <column list>.
Sometimes we need to remove one or more records from the table. The DML
command DELETE is used to remove individual or a set of rows from a table. The
rows which are to be deleted are selected by using the WHERE clause. If the WHERE
clause is not used, all the rows in the table will be deleted. The DELETE command
removes only records and not the individual field values.
The syntax of the DELETE
command is:
DELETE FROM <TABLE_NAME> [WHERE<condition> ];
4. Explain any two DDL commands.
CREATE TABLE and ALTER TABLE
The DDL command CREATE TABLE is used to define a table by specifying the name of the table and giving the column definitions consisting of name of the column, data type and size, and constraints if any, etc. Remember that each table must have at least one column.
The syntax of CREATE TABLE command is:
CREATE TABLE <table_ name> (<column_name> <data_type> [<constraint>]
[,<column_name><data_type>[<constraint>,]
..............................
.............................. );
If we want to add a column at the first position, use the clause FIRST. If we want to add a column at a specified position, use the clause ALTER <column _name>. Note that if we do not specify the position of the new column, then by default, it will be added as the last column of the table. Remember that the newly added column contains only NULL values.
syntax: ALTER TABLE <table name> ADD <column name> <datatype >[<size>] [<constraint>] [FIRST | AFTER <column name>];
5. What is a view ?How can we create a view using SQL statement ?
MySQL supports the concept of views, which is a feature of RDBMS. A view is a virtual table that does not really exist in the database, but is derived from one or more tables. The table(s) from which the tuples are collected to constitute a view is called base table(s). These tuples are not physically stored anywhere, rather the definition of the view is stored in the database. Views are just like windows through which we can view the desired information that is actually stored in a base table.
A view can be created with the DDL command CREATE VIEW.
The syntax is: CREATE VIEW <view name>
AS SELECT <column name1> [column name2]......FROM <table name> [WHERE <condition> ] ;
6……….. clause of SELECT query is used to apply condition to form
groups of records.
(a) Order by
(b) group by
(c) having
(d) where
Ans. c
7. a. Define constraints in SQL.
b. explain any 4 constraints
c. Compare column constraints and table constraints.
Constraints are the rules enforced on data that are entered into the column of a table. When we create a table, we can apply constraints on the values that can be entered into its fields. If this is specified in the column definition, SQL will not accept any values that violate the criteria concerned. This ensures the accuracy and reliability of the data in the database. The constraints ensure database integrity and hence they are often called data base integrity constraints. Constraints could be column level or table level.
b. Column constraints are applied only to individual columns. They are written immediately after the data type of the column. The following are column constraints:
i. NOT NULL This constraint specifies that a column can never have NULL values. NULL is a keyword in SQL that represents an empty value. It is important to remember that NULL does not equate to a blank or a zero; it is something else entirely. Though a blank is equal to another blank and a zero is equal to another zero, a NULL is never equal to anything, not even another NULL. Two NULL values cannot be added, subtracted or compared.
ii. AUTO_INCREMENT MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature. If no value is specified for the column with AUTO_INCREMENT constraint, then MySQL will assign serial numbers automatically and insert the newly assigned value in the corresponding column of the new record. By default, the starting value for AUTO_INCREMENT is 1, and it will be incremented by 1 for each new record. This special behavior also occurs if we explicitly assign the value NULL to the column. The AUTO_INCREMENT feature makes it easy to assign a unique ID to each new row, because MySQL generates the values for us. The auto increment column must be defined as the primary key of the table. Only one AUTO_INCREMENT column per table is allowed.
iii. UNIQUE It ensures that no two rows have the same value in the column specified with this constraint.
iv. PRIMARY KEY This constraint declares a column as the primary key of the table. This constraint is similar to UNIQUE constraint except that it can be applied only to one column or a combination of columns. The primary keys cannot contain NULL values. In other words, it can be considered as a combination of UNIQUE and NOT NULL constraints. A PRIMARY KEY constraint is used to enforce a rule that a column should contain only unique, non-NULL data.
c. Column constraints are applied only to individual columns. They are written immediately after the data type of the column.
Table constraints are similar to column constraints; the main difference is that table constraints can be used not only on individual columns, but also on a group of columns. When a constraint is to be applied on a group of columns of a table, it is called table constraint. The table constraint appears at the end of the table definition.
8.Define the following
DDL (Data definition language)
DML (Data manipulation language)
DCL (Data control language)
DDL is a component of SQL that provides commands to deal with the schema (structure) definition of the RDBMS. The DDL commands are used to create, modify and remove the database objects such as tables, views and keys. The common DDL commands are CREATE, ALTER, and DROP.
DML is a component of SQL that enhances efficient user interaction with the database system by providing a set of commands. DML permits users to insert data into tables, retrieve existing data, delete data from tables and modify the stored data. The common DML commands are SELECT, INSERT, UPDATE and DELETE.
Data Control Language (DCL) is used to control access to the database, which is very essential to a database system with respect to security concerns. DCL includes commands that control a database, including administering privileges and committing data. The commands GRANT and REVOKE are used as a part of DCL.
GRANT : Allows access privileges to the users to the database.
REVOKE : Withdraws user's access privileges given by using GRANT command.
9. Write the result of the following
ALTER TABLE <table_name> DROP <column_name>
DELETE * from <table_name>
DROP TABLE <table_name>
Ans. Removing column from a table. If we want to remove an existing column from a table, we can use DROP clause along with ALTER TABLE command.
Delete rows from a table.That is all the rows in the table will be deleted. The DELETE command removes only records and not the individual field values.
Removing table from a database.If we do not need a table, it can be removed from the database using DROP TABLE command.
(a) UNIQUE
(b) DISTNICT
(c) NOT NULL
(d) PRIMARY
Ans.b
2. How will you add a new column to an existing table using SQL Statement?
If we want to add a column at the first position, use the clause FIRST. If we want to add a column at a specified position, use the clause ALTER <column _name>
3. .Explain the SQL statements used to insert and delete data from a table.
The DML command INSERT INTO is used to insert tuples into tables.
The syntax is:
INSERT INTO <TABLE_NAME>
4. Explain any two DDL commands.
CREATE TABLE and ALTER TABLE
The DDL command CREATE TABLE is used to define a table by specifying the name of the table and giving the column definitions consisting of name of the column, data type and size, and constraints if any, etc. Remember that each table must have at least one column.
The syntax of CREATE TABLE command is:
CREATE TABLE <table_ name> (<column_name> <data_type>
If we want to add a column at the first position, use the clause FIRST. If we want to add a column at a specified position, use the clause ALTER <column _name>
5. What is a view ?How can we create a view using SQL statement ?
MySQL supports the concept of views, which is a feature of RDBMS. A view is a virtual table that does not really exist in the database, but is derived from one or more tables. The table(s) from which the tuples are collected to constitute a view is called base table(s). These tuples are not physically stored anywhere, rather the definition of the view is stored in the database. Views are just like windows through which we can view the desired information that is actually stored in a base table.
A view can be created with the DDL command CREATE VIEW.
The syntax is: CREATE VIEW <view name>
6……….. clause of SELECT query is used to apply condition to form
groups of records.
(a) Order by
(b) group by
(c) having
(d) where
Ans. c
7. a. Define constraints in SQL.
b. explain any 4 constraints
c. Compare column constraints and table constraints.
Constraints are the rules enforced on data that are entered into the column of a table. When we create a table, we can apply constraints on the values that can be entered into its fields. If this is specified in the column definition, SQL will not accept any values that violate the criteria concerned. This ensures the accuracy and reliability of the data in the database. The constraints ensure database integrity and hence they are often called data base integrity constraints. Constraints could be column level or table level.
b. Column constraints are applied only to individual columns. They are written immediately after the data type of the column. The following are column constraints:
i. NOT NULL This constraint specifies that a column can never have NULL values. NULL is a keyword in SQL that represents an empty value. It is important to remember that NULL does not equate to a blank or a zero; it is something else entirely. Though a blank is equal to another blank and a zero is equal to another zero, a NULL is never equal to anything, not even another NULL. Two NULL values cannot be added, subtracted or compared.
ii. AUTO_INCREMENT MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature. If no value is specified for the column with AUTO_INCREMENT constraint, then MySQL will assign serial numbers automatically and insert the newly assigned value in the corresponding column of the new record. By default, the starting value for AUTO_INCREMENT is 1, and it will be incremented by 1 for each new record. This special behavior also occurs if we explicitly assign the value NULL to the column. The AUTO_INCREMENT feature makes it easy to assign a unique ID to each new row, because MySQL generates the values for us. The auto increment column must be defined as the primary key of the table. Only one AUTO_INCREMENT column per table is allowed.
iii. UNIQUE It ensures that no two rows have the same value in the column specified with this constraint.
iv. PRIMARY KEY This constraint declares a column as the primary key of the table. This constraint is similar to UNIQUE constraint except that it can be applied only to one column or a combination of columns. The primary keys cannot contain NULL values. In other words, it can be considered as a combination of UNIQUE and NOT NULL constraints. A PRIMARY KEY constraint is used to enforce a rule that a column should contain only unique, non-NULL data.
c. Column constraints are applied only to individual columns. They are written immediately after the data type of the column.
Table constraints are similar to column constraints; the main difference is that table constraints can be used not only on individual columns, but also on a group of columns. When a constraint is to be applied on a group of columns of a table, it is called table constraint. The table constraint appears at the end of the table definition.
8.Define the following
DDL (Data definition language)
DML (Data manipulation language)
DCL (Data control language)
DDL is a component of SQL that provides commands to deal with the schema (structure) definition of the RDBMS. The DDL commands are used to create, modify and remove the database objects such as tables, views and keys. The common DDL commands are CREATE, ALTER, and DROP.
DML is a component of SQL that enhances efficient user interaction with the database system by providing a set of commands. DML permits users to insert data into tables, retrieve existing data, delete data from tables and modify the stored data. The common DML commands are SELECT, INSERT, UPDATE and DELETE.
Data Control Language (DCL) is used to control access to the database, which is very essential to a database system with respect to security concerns. DCL includes commands that control a database, including administering privileges and committing data. The commands GRANT and REVOKE are used as a part of DCL.
GRANT : Allows access privileges to the users to the database.
REVOKE : Withdraws user's access privileges given by using GRANT command.
9. Write the result of the following
ALTER TABLE <table_name> DROP <column_name>
DELETE * from <table_name>
DROP TABLE <table_name>
Ans. Removing column from a table. If we want to remove an existing column from a table, we can use DROP clause along with ALTER TABLE command.
Delete rows from a table.That is all the rows in the table will be deleted. The DELETE command removes only records and not the individual field values.
Removing table from a database.If we do not need a table, it can be removed from the database using DROP TABLE command.
