You can Download Chapter 14 SQL Commands Questions and Answers, Notes, 2nd PUC Computer Science Question Bank with Answers Karnataka State Board Solutions help you to revise complete Syllabus and score more marks in your examinations.
Karnataka 2nd PUC Computer Science Question Bank Chapter 14 SQL Commands
2nd PUC Computer Science SQL Commands One Mark Questions and Answers
The SQL is expanded as ‘Structured Query Language’.
Give the syntax for create command in SQL.
CREATE TABLE tablename (columnnamel datatype(size), columnname2 datatype(size) …);
What is drop command in SQL.
The drop command is used to remove/delete tables,
DROP TABLE tablename;
Give the command to display all the details in the table.
To view All the Columns and all the Rows (Entire Table values)
> SELECT * FROM student;
What is update command?
The update command is used to change row values from a table. The SET keyword takes the column in which values needs to be changed or updated
What is commit command?
The commit command is used to save the transactions entered into the table.
2nd PUC Computer Science SQL Commands Two Mark Questions and Answers
Classify numeric and character string data types in SQL.
- Numeric data type is classified as exact numeric data types and floating-point numeric data types.
- Character, string data types is classified as char and varchar data types.
Classify various SQL operators.
The various SQL operators are Arithmetic operators, Comparison operators, Logical operators, Operators used to negate conditions.
Which are the logical operators in SQL.
The logical operators in SQL are ALL, AND, ANY BETWEEN, EXISTS, IN, LIKE, NOT, OR, IS NULL, UNIQUE.
How do you modify the column name and width for existing table?
ALTER TABLE tablename MODIFY (columnname datatype (size), columnname datatype(size)..);
Write the syntax for distinct command in SQL.
SELECT DISTINCT columnname FROM tablename;
What is the use of NULL value?
A field with a value of NULL means that the field actually has no value stored in it.
What is create view command?
A view is referred to as a virtual table. Views are created by using the CREATE VIEW statement.
What is the dual table?
It is single row and single column dummy table provided by oracle.
2nd PUC Computer Science SQL Commands Three Mark Questions and Answers
Explain the features of SQL.
The features of SQL:
- SQL is ah ANSI and ISO standard computer language for creating and manipulating databases.
- SQL allows the user to create, update, delete, and retrieve data from a database.
- SQL is very simple and easy to learn.
- SQL works with database programs like DB2, Oracle, MS Access, Sybase, MS SQL Server, etc.
- SQL Queries can be used to retrieve large amounts of records from a database quickly and efficiently.
- Well, Defined Standards Exist.
List the components of SQL architecture.
When executing an SQL command for any RDBMS, the SQL engine interprets the task for execution. There are various components included in the process. These components of SQL architecture are Query Dispatcher, Optimization Engines, Classic Query Engine, and SQL Query Engine, etc.,
Explain DDL commands with example.
DDL -Data Definition Language commands create database objects such as tables, views, etc., The various DDL commands are Create Table, Alter Table, Create View, Drop Table.
CREATE TABLE table_name
( field1 datatype [ NOT NULL ],
field2 datatype [ NOT NULL ],
field3 datatype [ NOT NULL ]…)
An example of a CREATE TABLE statement follows.
SQL> CREATE TABLE BILLS (
2 NAME CHAR(30),
3 AMOUNT NUMBER,
4 ACCOUNT_ID NUMBER);
The ALTER TABLE command is used to do two things:
- Add a column to an existing table
- Modify a column that already exists
ALTER TABLE table_name ADD( column_name data_type);
ALTER TABLE table_name MODIFY (column_name data_type);
The following command changes the NAME field of the BILLS table to hold 40 characters:
SQL> ALTER TABLE BILLS MODIFY (NAME CHAR(40));
DROP command is used to remove the entire table from the database.
DROP TABLE tablename;
DROP TABLE student;
Explain DML commands with example.
The data manipulation commands are used for retrieval (view) of data, insertion of new data, modification of data or deletion. The DML commands includes insert, delete and update.
1. INSERT command:
It is used to inserts new rows into the table.
INSERT INTO tablename (columnname1, columnname2, … ) VALUES ( value1, value2, ..);
INSERT INTO student (regno, name, Combn , fees ) VALUES (1234, ‘Hemanth’, ‘PCMCs’, 15000);
2. UPDATE command:
It can be used to change row values from a table. The SET key word takes the column in which values needs to be changed or updated. The WHERE keyword is used to filter the records on some condition.
UPDATE tablename SET columnname = values WHERE Condition;
UPDATE student SET combn = ‘PCMCs’ where combn=’pcmc’;
3. DELETE Command:
It is used to delete/remove the tuples/rows from the table. All the rows will be deleted if WHERE clause is not used in the statement otherwise it selects the rows for delete which satisfies the condition.
DELETE from tablename WHERE Condition;
DELETE from student;
DELETE from student WHERE regno=1234;
Explain with an example boolean expression in SQL.
Boolean expressions return rows (results) when a single value is matched. Boolean expressions commonly used in a WHERE clause are made of operands operated on by SQL operators.
> SELECT * FROM EMPLOYEES WHERE AGE = 45;
The above statement returns all those records (rows) whose age column is having the exact value of 45 from the employees table.
Explain AND operator using where in SQL.
The operator AND means that the expressions on both sides must be true to return TRUE. If either expression is false AND returns FALSE.
SELECT column1, column2, columnN FROM table_name
WHERE condition1 AND condition2 … AND conditionN;
It would fetch ID, Name and Salary fields from the CUSTOMERS table where salary is greater than 2000 AND age is less than 25 years:
SQL> SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE SALARY > 2000 AND age < 25;
To find out which employees have been with the company for 5 years or less and have taken more than 20 days leave,:
SQL> SELECT empNAME FROM VACATION WHERE YEARS <= 5 AND LEAVETAKEN > 20;
List the built-in functions associated with Group functions in SQL.
The built-in functions associated with GROUP functions in SQL are
1. COUNT function- returns the count of records that satisfies the condition for each group of records.
SELECT department, COUNT(*)FROM employees WHERE salary > 25000 GROUP BY department;
2. MAX function- returns the maximum values from the column for each group of records.
SELECT department, MAX(salary) FROM employees GROUP BY department;
3. MIN function – returns the lowest values from the column for each group of records.
SELECT department, MIN(salary)FROM employees GROUP BY department;
4. AVG function – returns the average values from the column for each group of records.
SELECT AVG(cost) FROM products WHERE category = ‘Clothing’;
5. SUM function- returns the total values from the column for each group of records.
SELECT department, SUM(sales)FROM order_details GROUP BY department;
6. DISTINCT function – returns the once occurrence of many repeated values from the column for each group of records.
SELECT AVG(DISTINCT cost)FROM products WHERE category = ‘Clothing’;
What is the use of JOIN command?
SQL joins are used to combine rows from two or more tables.
There are 4 different types of SQL joins:
- SQL INNER JOIN (or sometimes called simple join)
- SQL LEFT JOIN
- SQL RIGHT JOIN
- SQL FULL JOIN
1. SQL INNER JOIN (simple join)
SQL INNER JOINS return all rows from multiple tables where the join condition is met.
> SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;
In this visual diagram, the SQL INNER JOIN returns the shaded area:
The SQL INNER JOIN would return the records where table1 and table2 intersect.
2. SQL LEFT JOIN:
This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condi¬tion is met).
> SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column;
In this visual diagram, the SQL LEFT JOIN returns the shaded area:
The SQL LEFT JOIN would return the all records from table1 and only those records from table2 that intersect with table1.
3. SQL RIGHT JOIN:
This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).
> SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;
In this visual diagram, the SQL RIGHT JOIN returns the shaded area:
The SQL RIGHT JOIN would return the all records from table2 and only those records from table1 that intersect with table2.
4. SQL FULL JOIN:
This type of join returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the join condition is not met.
> SELECT columns FROM table1 FULL JOIN table2 ON table1.column = table2.column;
In this visual diagram, the SQL FULL JOIN returns the shaded area:
The SQL FULL JOIN would return the all records from both table1 and table2.
What are privileges and roles?
The Privileges defines the access rights given to a user on a database object. There are two types of privileges.
- System privileges – This allows the user to CREATE, ALTER, or DROP database objects.
- Object privileges – This allows the user to EXECUTE, SELECT, INSERT, UPDATE, or DELETE data from database objects to which the privileges apply.
Few CREATE system privileges are listed below:
CREATE object – allows users to create the specified object in their own schema.
CREATE ANY object – allows users to create the specified object in any schema.
Few of the object privileges are listed below:
- INSERT – allows users to insert rows into a table.
- SELECT – allows users to select data from a database object.
- UPDATE – allows user to update data in a table.
- EXECUTE – allows user to execute a stored procedure or a function.
Roles are a collection of privileges or access rights. When there are many users in a database it becomes difficult to grant or revoke privileges to users. Therefore, if roles are defined, one can grant or revoke privileges to users, thereby automatically granting or revoking privileges.
Some of the privileges granted to the system roles are as given below:
1. CONNECT – CREATE TABLE, CREATE VIEW, CREATE SYNONYM, CREATE SEQUENCE, CREATE SESSION, etc.
2. RESOURCE – CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIG-GER, etc.
The primary usage of the RESOURCE role is to restrict access to database objects. DBA- ALL SYSTEM PRIVILEGES.
Classify various built-in functions in SQL.
The built-in functions are classified as Single row functions and Group functions.
The single-row functions are of four types. They are numeric functions, character or text functions, date functions, and conversion functions.
1. Numeric functions:
ABS function- The ABS function returns the absolute value of the number you point to.
a. CEIL and FLOOR functions – CEIL returns the smallest integer greater than or equal to its argument. FLOOR does just the reverse, returning the largest integer equal to or less than its argument.
b. POWER function – To raise one number to the power of another, use POWER. In this function the first argument is raised to the power of the second:
c. SQRT function – The function SORT returns the square root of an argument.
2. Character or text functions:
a. CHR function – returns the character equivalent of the number it uses as an argument.
b. CONCAT function – function combines two strings together.
c. INITCAP function – capitalizes the first letter of a word and makes all other characters lowercase.
d. LOWER and UPPER functions – LOWER changes all the characters to lowercase; UPPER does just the reverse.
e. LENGTH function – returns the length of its lone character argument.
3. Date functions:
a. ADD_MONTHS function – This function adds a number of months to a specified date.
b. LAST_DAY – LAST_DAY function- returns the last day of a specified month. MONTHS_BETWEEN function – to know how many months fall between month x and month y,
c. NEXT_DAY function – finds the name of the first day of the week that is equal to or later than another specified date.
d. SYSDATE function – SYS DATE returns the system time and date.
4. Conversion functions:
a. TO_CHAR function – to convert a number into a character.
b. TO_NUMBER function – it converts a string into a number.
The group functions are:
- AVG() – Returns the average value
- COUNT() – Returns the number of rows
- FIRST() – Returns the first value
- LAST() – Returns the last value
- MAX() – Returns the largest value
- MIN() – Returns the smallest value
- SUM() – Returns the total value of the given column.
2nd PUC Computer Science SQL Commands Five Mark Questions and Answers
Explain SQL constraints with example.
SQL Constraints are rules used to limit the type of data that can be stored into a table, to maintain the accuracy and integrity of the data inside table.
Constraints can be divided into two types,
- Column level constraints: limits only column data
- Table level constraints: limits whole table data
Constraints are used to make sure that the integrity of data is maintained in the database. The NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT are the most used constraints that can be applied to a table.
1. NOT NULL Constraint:
NOT NULL constraint restricts a column from having a NULL value. Once *NOT NULL* constraint is applied to a column, you cannot store null value to that column. It enforces a column to contain a proper value.
Example using NOT NULL constraint
CREATE table Student(s_id int NOT NULL, Name varchar(60), Age int);
2. UNIQUE Constraint:
UNIQUE constraint ensures that a field or column will only have unique values. A UNIQUE constraint field will not have duplicate data.
Example using UNIQUE constraint when creating a Table
CREATE table Student(s_id int NOT NULL UNIQUE, Name varchar(60), Age int);
3. Primary Key Constraint:
The primary key constraint uniquely identifies each record in a database. A Primary Key must contain unique value and it must not contain a null value.
Example using PRIMARY KEY constraint
CREATE table Student (s_id int PRIMARY KEY, Name varchar(60) NOT NULL, Age int);
4. Foreign Key Constraint:
FOREIGN KEY is used to relate two tables. A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
Example using FOREIGN KEY constraint at Table Level
CREATE TABLE Orders
O-Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
5. CHECK Constraint:
CHECK constraint is used to restrict the value of a column between a range. It performs check on the values, before storing them into the database.
Example using CHECK constraint
create table Student(s_id int NOT NULL CHECK(s_id > 0), Name varchar(60) NOT NULL, Age int);
Explain with example to create details of employees and give the minimum and maximum in the salary domain.
The CHECK constraint is used to limit the value range that can be placed in a column.
If you define a CHECK constraint on a single column it allows only certain values for this column.
Check Constraint at column level:
CREATE TABLE employee
( id number(5) PRIMARY KEY,
salary number(lO) CHECK (salary >=5000 AND salary <=40000),
In the above example, the salary column is defined as a number column with a check constraint. It checks 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 the maximum value for the column salary.
Write the differences between order by and group by with example.
1. SQL ORDER BY Clause:
The SQL ORDER BY Clause is used in a SELECT statement to sort results either in ascending or descending order.
Syntax for using SQL ORDER BY clause to sort data is:
>SELECTcolumn-list FROM table_name ORDER BY column1, column2,.. columnN [DESC]];
If you want to sort the employee table by salary of the employee, the SQL query would be.
> SELECT name, salary FROM employee ORDER BY salary;
By default, the ORDER BY Clause sorts data in ascending order. If data to be sorted in descending order, the command would be as given below.
> SELECT name, salary FROM employee ORDER BY name, salary DESC;
2. SQL GROUP BY Clause:
The SQL GROUP BY Clause is used with the group functions to retrieve data grouped according to one or more columns.
The basic syntax of GROUP BY clause is given below. The GROUP BY clause must follow the conditions in the WHERE clause and must precede the ORDER BY clause if one is used.
SELECT column1, column2 FROM table name WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2
If you want to know the total amount of salary spent on each department, the query would be:
> SELECT dept, SUM (salary) FROM employee GROUP BY dept;
The group by clause should contain all the columns in the select list expect those used along with the group functions.
> SELECT location, dept, SUM (salary) FROM employee GROUP BY location, dept;