2nd PUC Computer Science Question Bank Chapter 14 SQL Commands

Students can Download 2nd PUC Computer Science Chapter 14 SQL Commands Questions and Answers, Notes Pdf, 2nd PUC Computer Science Question Bank with Answers helps you to revise the complete Karnataka State Board Syllabus and to clear all their doubts, score well in final exams.

Karnataka 2nd PUC Computer Science Question Bank Chapter 14 SQL Commands

2nd PUC Computer Science SQL Commands One Mark Questions and Answers

Question 1.
Expand SQL.
Answer:
Structured Query Language.

Question 2.
Give the syntax for create command in SQL.
Answer:
Create table table-name (
Column – 1 datatype.
Column – 2 datatype,
Column – 3 datatype,
……………….
……………….
Column N datatype
Primary Key (one or more columns)
);

Question 3.
What is drop command is SQL?
Answer:
Drop command is used to remove a table definition and all data, indexes, triggers. Constraints, and permission specification for that table.

Question 4.
Give the command to display all the details in the table.
Answer:
SELECT * FROM table – name;

Question 5.
What is update command?
Answer:
The SQL update Query is used to modify the existing records in a table.

Question 6.
What is commit command?
Answer:
The commit command-is the transactional command used to save changes invoked by a transaction to the database.

2nd PUC Computer Science SQL Commands Two Marks Questions and Answers

Question 1.
Classify Numeric and character string data types in SQL.
Answer:
Numeric data types:
Int and Numeric are two numeric data types. Int data type from – 2,147, 483,647, and Numeric data type from – 10^38 + 1 to 10^38 – 1 [-10³⁸ + 1 to + 10³⁸ – 1]
Charcecter string data type:
Char and varchar are two character string datatypes.
Char data type → Maximum length of 8,000 characters (Fixed length non – Unicode character).
varchar datatype → Maximum at 8,000 characters (variable length non-unicode data).

Question 2.
Classify various SQL operators?
Answer:
Different SQL operators are:

1. Arithmetic operator
2. Comparison operator
3. Logical operator
4. Operators used to negate conditions.

Question 3.
Which are the logical operators in SQL.
Answer:

• ALL
• AND
• ANY
• BETWEEN
• EXISTS
• IN
• LIKE
• NOT
• OR
• IS NULL
• UNIQUE.

Question 4.
How do you modify the column name and with for existing table?
Answer:
The table can be modified or changed by using the Alter command. The comment is ALTER table. Table name (column name data type (size); SQL > alter table employee modify salary number (15, 2); Table altered.

Question 5.
Write the syntax for distinct commands in SQL.
Answer:
SELECT DISTINCT column – 1, column – 2, …….., column – N,
From table – name
WHERE [condition]

Question 6.
What is the used of NULL value?
Answer:
A Null value in a table is a value in a field that appears to be blank i.e a field with a NULL value is a field;with no value. A NULL value is different or a field that contains spaces.

Question 7.
What is create view command?
Answer:
Create view command is used to create database views in SQL by using CREATE VIEW statements. Views can be created from a single table, multiple tables or another view. To create a view, a user must have the appropriate system privilege according to the specific implementation.

Question 8.
What is Dual table?
Answer:
Dual table is a single row and single column dummy table provided by oracle. This is used to perform mathematical calculations without using a table.

2nd PUC Computer Science SQL Commands Three Marks Questions and Answers

Question 1.
Explain the features of SQL?
Answer:

1. Creating tables in SQL.
2. Selecting attribute data type and domains
3. Specifying constraints.
4. Schema change statements.

Question 2.
List the components of SQL architecture.
Answer:

1. SQL Query
2. Query Language processor
3. DBMS Engine
4. Physical Database
5. Parser + Optimizer
6. File manager + Transaction manager.

Question 3.
Explain DDI commands with example.
Answer:
Create: Creates a new table, a view of table, or other object in a database.
Ex:
CREATE TABLE EMPLOYEES(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18,2).
PRIMER KEY (ID)
);
ALTER:
Modifies an existing database object, such as a table. Ex: ALTER TABLE EMPLOYEES MODIFY SALARY NUMBER (15,2);

DROP:
Deletes an entire table, a view of a table or other object in the database. Ex: DROP TALE EMPLOYEES;

Question 4.
Explain DML commands with examples.
Answer:
DML (Data manipulation language) provides the data manipulation techniques like selection, insertion, deletion, update, modification, replacement, retrieval, sorting & display of data or records.
INSERT: Create records
Ex: INSERT INTO EMPLOYEES (ID, NAME, AGE, ADDRESS, SALARY) VALUES (1, ‘PRANITA’. 32, ‘BIJAPUR’, 50000.00);
UPDATE: Modifies records.
Ex: UPDATE EMPLOYEES SET ADDRESS – ‘BANGALORE’ WHERE ID – 5;
DELETE Deletes records.
Ex: DELETE FROM EMPLOYEES WHERE ID – 4;

Question 5.
Explain with an example Boolean expression in SQL.
Answer:
SQL Boolean expressions fetch the data on the basis of matching single value.
Following is the syntax: SELECT column1, column2, columnN
From table – name
WHERE SINGLE VALUE MATCHING EXPRESSION;
Consider the Employees table having the following records. Here is simple example showing usage of SQL Boolean expression.
SQL SELECT * FROM EMPLOYEES WHERE AGE = 32;

Question 6.
Explain AND operator using where in SQL.
Answer:
The and operator allows the existence of multiple conditions in an SQL statement’s WHERE clause.
Syntax: The basic syntax of AND operator with WHERE clause is as follows;
SELECT column 1, column 2, column N,
From table – name
WHERE [condition] and [condition 2] ……. and [condition N];

Question 7.
List the built-in functions associated with Group fanctions is SQL.
Answer:
Group functions are built-in SQL functions that operate on groups of rows and return one value for the entire group.
These functions are: COUNT, MAX, MIN, AVG, SUM, DISTINCT.

1. SQL COUNT(): This function returns the number of rows in the table that satisfies the condition specified in the where condition.
   SELECT COUNT (*) FROM EMPLOYEE
2. SQL DISTINCT (): This function is used to select distinct rows.
   SELECT COUNT (DISTINCT NAME ) FROM EMPLOYEE:
3. SQL MAX (): This function is used the get the maximum value from a column.
   SELECT MAX (SALARY) PROM EMPLOYEE;
4. SQL MIN (): This function is used to get the minimum value from a column.
   SELECT MIN (SALARY) FROM EMPLOYEE:
5. SQL AVG (): This function is used to get the average value of a numeric column.
   SELECT AVG (SALARY) FROM EMPLOYEE;
6. SQL SUM (): This function is used to get the sum of numeric value.
   SELECT SUM (SALARY) FROM EMPLOYEE;

Question 8.
What is the use to join command?
Answer:
Joins clause is used to combine records from two or more tables in a database. A Join is a means for combining fields from two tables by using values common to each.
Consider the following two tables.

1. Sports table is as follows.
2. Another table is ORDER – items as follows.

Now let us Join these tables in our SELECT statements as follows: This would produce the following result.
Here, it is noticeable that the join is performed in the WHERE clause. Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN LIKE and NOT they can be used to join tables. However, the most common operator is the equal symbol.

Question 9.
What are the privileges and rules?
Answer:
Privileges:
Privileges defines the access rights provided to a used on a database object. There are two types of privileges.

1. System privileges: This allows the user to Create, Alter, or Drop Database objects.
2. Object privileges: This allows the used to EXECUTE, SELECT, INSERT, UPDATE OR DELETE data from database objects to which the privileges apply.

Question 10.
Classify various built in functions in SQL.
Answer:
There are main two types of functions in SQL oracle version.

1. Single row functions: Single row or scalar functions return a value for every row that is processed in a query.
2. Group functions: These functions group the rows of databased on the values returned by the query. Then are four types of single row functions.
   • Numberic function: These are functions that accent numeric input and return numeric values. ex:- ABS(), Round (), Trunc()
   • Character Text functions: These are functions that accept character input and can return both character and number values. ex: Lower (), Upper (), Length ().
3. Data functions: These are functions that take values that are of datatype DATE as input and return values of data type DATE, except for the MONTPIS – BETWEEN function, which returns a number. Example: Next – day (), Last – day ()

2nd PUC Computer Science SQL Commands Five Marks Questions and Answers

Question 1.
Explain SQL contraints with examples.
Answer:
Constraints are the rules enforced on data columms on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database. The constraints available in SQL are Foreign key, Not null, unique Check constraints can be defined in to ways.

1. a) The contraints can be specified immediately after the colum definition. This is called column-level definition.
2. b) This contraints can be specified after all the columns are defined. This is called table – level definition.

SQL Primary key:
This constraints defines a column or combination of columns which uniquely identifies each row in the table.
For Example:
CREATE TABLE EMPLOYEE
( ID NUMBER (6) PRIMARY KEY, NAME CHAR (20) dept char (15), age number (2), salary number (10), city char (15)
);

Foreign key or Referential Integrity:
This constraint identifies any column referencing the primary key in another table. It establishes a relationship between two columns in the same table or between different tables.
For example:
Lets use the ‘Sports’ table and ‘Order – items’.
Foreign key at column level: CREATE TABLE PRODUCT
(Product-id number (8) CONSTRAINT pd-id-Pk PRIMARY KEY. Product-name char (20), supplier-name char (20), supplier-name char (20), unity-price number (12)
);

Notnull Contraints:
This contraints ensures all rows in the table contain a definite value for the column which is specified as notnull.
For example:
CREATE TABLE EMPLOYEE
(id number (s), name char (20) Constraint nm-nn NOT NULL, dep char (10), age number (2), salary number (10), city char(15)
);

Unique key:
This constraint ensures that a column or a group of columns in each row have a distinct value.

Cheek constraints:
This contrasts defines a business rule on a column. All the rows must satisfy this rule.

Question 2.
Explain with the example to create details of employees and give the minimum and maximum in the salary domain.
Answer:
To create an employee table with a primary key constraint, the query would be like.
CHECK constraint at column level
CREATE TABLE employee
(id number (5) PRIMARY KEY,
name char (20)
dept char (10)
age number (2)
gender char (1)
salary number (10) CHECK (Salary > = 5000 AND salary < = 40000) Location char (10)
s);
The salary column is defined as number column with a check constraint. It check constraint checks the value that can be stored in the salary column should be greater than or equal to 5000 which is the minimum value and the value is less than or equal to 40000 which is maximum value for the column salary.

Question 3.
Write the differences between order by and group by with example.
Answer:
ORDER BY:
This clause is used to sort the data in ascending or deseeding order, based on one or more columns. Some database sorts query results in ascending order by default.
Syntax:
SELECT column -list
From table-name [wher condition]
[ORDER by column 1, column 2, …….. column N] [ASC/DESC];
SQL> select id, name, salary from employees order by salary;

GROUP BY:
This clause is used in collaboration with the select statement to arrange the identical data into groups. The group by clause follows the WHERE CLAUSE in a SELECT statement and precedes the ORDER by clause.
Syntax:
SELECT column 1, column 2
FROM table-name
WHERE [conditions]
GROUP BY column 1, column2
ORDER BY column 1, column2
Ex: Select id name from Employee group by department);

2nd PUC Computer Science SQL Commands Additional Questions and Answers

Question 1.
Expand DDL.
Answer:
Data definition language.

Question 2.
Expand DML
Answer:
Data manipulation Language.

Question 3.
Name the SQL Command for deleting tuple from the database.
Answer:
Delete.

Question 4.
What is structured query Language?
Answer:
Structured query Language is a data base language which supports both data definition and data manipulation.

Question 5.
Explain select command is SQL.
Answer:
The select command is used to extract information out of the database. This command is used to perform queries. A query is a request for some information from the database.

• Select columname,…
• From tablename,…

[Where condition]
with this command it is possible for ns to either display all columns or only specific columns from the table. The select command can be used with a lot of variations.
Some of them are given below:
1. To extract the contents of the entire table type the following syntax:
Select* from tablename
Eg: select* from employ
will displav contents of employ table.

2. To extract certain columns from the table type the following syntax:
Selcet columnname, columnname from table name
Eg: Select empno, name from employ.
will display contents of only 2 columns from employ table.

3. To eliminate duplicates from display type following syntax:
This command is used to eliminate redundant data, which may have been added to the database.
The syntax is:
Select distinct columnname, columnaname from tablename
Eg: Select distinct empno Name from employ.

4. To Sort the data in a table type following syntax:
Select columnnme 1, columnname 2 from tablename order by columnname, The column name is the primary key. One can specify any number of keys.
Eg: Select empno, Name from employ order by empno

5. To select a specific set of data from the table type following syntax:
Select coulmnname, columnname, …….. from tablename where wearch condition.
Eg: Select empno, Name from employ where dept = ‘acc’

Question 6.
Explain drop and alter commands in SQL.
Answer:
Alter command can be used in the following situations:

1. When a user wants to add a new column.
2. When a user wants to modify the structure of an existing column.

1. Adding a new column to existing table:
Syntax: Alter tablename Add (New columname datatype (size)
Eg: To add the fields dept varchar 2 (10) & address varchar 2(25) to the table employ the command is:
Alter Table employ Add (dept varchar 2(10), address varchar 2 (25)

2. Modifying structure of an existing column:
Syntax: Alter table tablename modify (columnname newdata type (size))
Eg: To change the field address in the employ table, the command is:
Alter Table employ modify (address varchar 2 (40))
The above command is given to increase the size of the attribute address from a size 25 characters to a size of 40 characters.
Drop command can be used to remove unwanted tables from the database.
Syntax: Drop table table name. Eg: Drop Table employ

Question 7.
Explain Update & Delete Cammands in SQL.
Answer:
Update Command:
This command is used to alter values in an existing table. The syntax of this command is: Update tablename set field=value, ……..where condition: In the above defined command the ‘Set’ clause is compulsory as it is used to define the new value & the ‘where’ clause is optional & is used only when the update depends on a particular Criteria.
Eg: Update employ set da = basic*0.65 where basic > 6000
The above command will set da value of the employees whose basic salary is more than 6000 as 65% of basic.

Delete command:
It is necessary to delete centain rows, which are not required either due to redundancy or because wrong data values has been entered. Delete command is used in order to perform above activity.
syntax:
Delete from tablename where condition Eg: delete from employ where empno > 120 will delete all records meeting the given criteria.