Download Database Management Systems notes and more Exercises Database Management Systems (DBMS) in PDF only on Docsity!
MAJLIS ARTS AND SCIENCE COLLEGE
DEPARTMENT OF COMPUTER SCIENCE
DBMS
(Questions and answer based on fourth module) (1 Mark questions)
- Which operator performs pattern matching? Ans: LIKE
- What operator tests column for the absence of data? Ans: IS NULL
- Which command is used to SELECT only one copy of each set? Ans: SELECT DISTINCT
- Which SQL keyword is used to sort the result set? Ans: ORDER BY ( 2 Mark questions)
- What is SQL Command? SQL commands are instructions, coded into SQL statements, which are used to communicate with the database to perform specific tasks, work, functions and queries with data.
- What is Data Definition Language (DDL)? These SQL commands are used for creating, modifying, and dropping the structure of database objects. The commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE.
- What is Data Manipulation Language (DML)? These SQL commands are used for storing, retrieving, modifying, and deleting data. These Data Manipulation Language commands are: SELECT INSERT, UPDATE, and DELETE.
- Explain Logical Operators used in SQL There are three Logical Operators namely, AND, OR, and NOT. These operators compare two conditions at a time to determine whether a row can be selected for the output. When retrieving data using a SELECT statement, you can use logical operators in the WHERE clause, which allows you to combine more than one condition. a) OR Logical Operator: If you want to select rows that satisfy at least one of the given conditions, you can use the logical operator, OR. b) AND Logical Operator: If you want to select rows that must satisfy all the given conditions, you can use the logical operator, AND.
c) NOT Logical Operator: If you want to find rows that do not satisfy a condition, you can use the logical operator, NOT.
- What is the use of INSERT command? INSERT statement is used to insert a single record or multiple records into a table in PostgreSQL. Syntax: INSERT INTO table (column1, column2, ... ) VALUES (vaue1,value2, ... ), (value1,value2, ... ), ………………………;
- What is the use of LIKE operator? OR Explain pattern matching in PostgreSQL? The LIKE operator is used to list all rows in a table whose column values match a specified pattern. For this purpose, we use a wildcard character '%'. Example To select all the students whose name begins with 'S' SELECT * FROM student_details WHERE sname LIKE 'S%'; There is another wildcard character you can use with LIKE operator. It is the underscore character, ' _ '. In a search string, the underscore signifies a single character. Example To display all the names with 'a' as second character, SELECT * FROM student_details WHERE sname LIKE '_a%';
- Explain UPDATE Command An SQL UPDATE statement changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition. Syntax: UPDATE table_name SET column_name = new_value WHERE some_condition; Example UPDATE product SET qty=50 WHERE pcode=’P0003’
- Differentiate between DELETE and DROP in SQL DELETE command is a Data Manipulation Language command whereas; DROP is a Data Definition Language Command. DELETE command is used to remove tuples/records from a table. The DROP command removes a table from the database. All the tables' rows, indexes, and privileges will also be removed. ( 5 Mark questions)
✓ EXTRACT(): This function retrieves a field such as a year, month, and day from a date/time value. Syntax EXTRACT(field FROM source) The field argument specifies which field to extract from the date/time value. CENTURY The century DAY The day of the month (1 - 31) MONTH Month, 1 - 1 2 YEAR The year DOW The day of week Sunday (0) to Saturday (6) DOY The day of year that ranges from 1 to 366 HOUR The hour (0-23) MINUTE The minute (0-59) SECOND The second Example SELECT EXTRACT (YEAR FROM TIMESTAMP '201 6-1 2-31 1 3:30:1 5') 201 6 ✓ DATE_PART (): This function that allows you to retrieve subfields e.g., year, month, week from a date or time value. Syntax DATE_PART(field,source) Example SELECT date_part('year',TIMESTAMP '201 7- 01 - 01 '); date_part 201 7 ✓ NOW(): This function returns the current date and time. Example SELECT NOW()
- Explain different string functions in SQL ✓ CHAR(): This function returns a character which is corresponding the ASCII code value Example SELECT CHR(65); ✓ LEFT(): This function returns the first n characters in the string. Syntax
LEFT(string, n) Example SELECT LEFT('ABC',1 ); left
A ✓ LENGTH() :This function returns the number of characters in the string. Example SELECT LENGTH(‘Computer’); LENGTH
8 ✓ REPLACE(): This functions that search and replace a substring with a new substring in a string. Syntax REPLACE(source, old_text, new_text ) Example REPLACE(‘malayalam’,’a’,’e’); REPLACE
Meleyelem ✓ SUBSTRING(): This function returns a part of string. Syntax SUBSTRING ( string ,start_position , length ) string is a string whose data type is char, varchar, text, etc start_position is an integer that specifies where you want to extract the substring. length is a positive integer that determines the number of characters that you want to extract from the string beginning at start_position Example SELECT SUBSTR(‘computer’,3,4); SUBSTR
- What is view? How it is created? A view is named query that provides another way to present data in the database tables. A view is defined based on one or more tables, which are known as base tables. When you create a view, you basically create a query and assign it a name. a PostgreSQL view is a logical table that represents data of one or more underlying tables through a SELECT statement. Syntax CREATE VIEW view_name AS SELECT column_list FROM table_name [WHERE condition]; Dropping Views To drop a view, simply use the DROP VIEW statement with the view_name.
RENAME COLUMN old_column_name TO new_column_name; ALTER TABLE student RENAME COLUMN dob TO d_birth
- Explain CREATE TABLE command The CREATE TABLE Statement is used to create tables to store data. Integrity Constraints like primary key, unique key, and foreign key can be defined for the columns while creating the table. The integrity constraints can be defined at column level or table level. Syntax: CREATE TABLE table_name (column_name1 datatype, column_name2 datatype, ... column_nameN datatype ); Example CREATE TABLE fruits (fname varchar(10),qty int);
- What is PRIMARY KEY? How can we set primary key using CREATE command? The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields). If the primary key consists of only one column, you can define use PRIMARY KEY constraint as a column constraint: CREATE TABLE TableName( ColumnName1 data_type PRIMARY KEY , ColumnName2 data_type, ... ColumnNameN data_type ); Example CREATE TABLE Student ( Admino varchar(5) PRIMARY KEY, sname varchar(20), age int); In case the primary key has two or more columns, you must use the PRIMARY KEY constraint as a table constraint: Syntax CREATE TABLE table_name ( ColumnName1 data_type, ColumnName2 data_type,
ColumnNameN data_type, CONSTRAINT Constrint_name PRIMARY KEY(coumn1,column2)); Example CREATE TABLE Sales_Order(itemcode varchar(5), orderno varchar(5), order_date date, qty int, CONSTRAINT pk_sorder PRIMARY KEY(itemcode.orderno))
- How to Create Primary Key - Using ALTER TABLE statement? You can create a primary key in PostgreSQL with the ALTER TABLE statement. Syntax ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2,….columnN) Example ALTER TABLE Student ADD CONSTRAIT pk_admno PRIMARY KEY(Admino)
- How to remove or drop primary key from a table? You can drop a primary key in PostgreSQL using the ALTER TABLE statement. Syntax ALTER TABLE table_name DROP CONSTRAINT constraint_name; Example ALTER TABLE Student DROP CONSTRAINT pk_admno;
- What is foreign key? How to set foreign key using CREATE TABLE command? A foreign key is a field or group of fields in a table that uniquely identifies a row in another table. In other words, a foreign key is defined in a table that references to the primary key of the other table. The table that contains the foreign key is called referencing table or child table. And the table to which the foreign key reference is called referenced table or parent table. In PostgreSQL, you define a foreign key through a foreign key constraint. A foreign key constraint indicates that values in a column or a group of columns in the child table match with the values in a column or a group of columns of the parent table. We say that a foreign key constraint maintains referential integrity between child and parent tables. Example
a new row, PostgreSQL checks if the value is already in the table. If it finds that the new value is already there, it rejects the change and issues an error. The same process is carried out for updating existing data. The syntax for creating a unique constraint using a CREATE TABLE statement in PostgreSQL is: CREATE TABLE table_name ( column1 datatype , column2 datatype , ... CONSTRAINT constraint_name UNIQUE (columname) ); Example CREATE TABLE Customer (Custno varchar(5), Custname varchar(20), email varvar(20), mobile varchar(10), CONSTRAINT unq_email UNIQUE(email), CONSTRAINT unq_mob UNIQUE(mobile))
- What is the difference between PRIMARY KEY and UNIQUE Constraint? A primary key is a column of table which uniquely identifies each row in that table. Only one primary key is allowed to use in a table. The primary key does not accept NULL values. A primary key of one table can be referenced by foreign key of another table. Unique key constraints also identify an individual row uniquely in a table. A table can have more than one unique key unlike primary key. Unique key constraints can accept only one NULL value for column. Unique constraints are also referenced by the foreign key of another table.
- What is constraint? Discuss different types of constraint used in PostgeSQL Constraints are the rules enforced on data columns on table. These are used to prevent invalid data from being entered into the database. This ensures the accuracy and reliability of the data in the database. Constraints could be column level or table level. Column level constraints are applied only to one column whereas table level constraints are applied to the whole table. The following are commonly used constraints available in PostgreSQL. - NOT NULL Constraint − Ensures that a column cannot have NULL value. - UNIQUE Constraint − Ensures that all values in a column are different. - PRIMARY Key − Uniquely identifies each row/record in a database table. - FOREIGN Key − Constrains data based on columns in other tables. - CHECK Constraint − The CHECK constraint ensures that all values in a column satisfy certain conditions.
( 10 Mark questions)
- Explain SELECT Command with example SELECT statement is used to fetch the data from a database table, which returns data in the form of result table. These result tables are called result-sets. The basic syntax of SELECT statement is as follows − SELECT column1, column2, columnN FROM table_name; Here, column1, column2...are the fields of a table, whose values you want to fetch. If you want to fetch all the fields available in the table then you c an use the following syntax − SELECT * FROM table_name; SELECT Statement with WHERE clause WHERE clause is used to specify a condition while fetching the data from tables. The basic syntax of SELECT statement with WHERE clause is as follows. SELECT column1, column2, columnN FROM table_name WHERE [search_condition] Following is a list of operators that can be used while specifying the WHERE clause condition. =,! =, <,>, <=,>=, BETWEEN, LIKE, IN SELECT Statement with ORDER BY clause ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. SELECT Statement with GROUP BY clause GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups. The GROUP BY clause is used in the SELECT statement in conjunction with aggregate functions to produce summary reports from the database. The HAVING clause is used to restrict the results returned by the GROUP BY clause. Example Consider the following table student (Admno, sname, age, gender) If we want to display all the details of the students write the following query SELECT * FROM student; If we want the name and age of students write the following query SELECT sname, age FROM student; If we want the details of male students only write the following query SELECT * FROM student WHERE gender=’Male’; If we want to display the details of students in the ascending order of name write the following query. SELECT * FROM student ORDER BY sname If we want to know the number of Male and Female students writes the following query SELECT gender, COUNT () FROM student GROUP BY gender; If we want to know the number of female students only writes the following query SELECT gender, COUNT ( _) FROM student GROUP BY gender HAVING gender=’Female’_
Syntax SELECT columns FROM table INNER JOIN table ON table1.column = table2.column; Visual Illustration ➢ FULL OUTER 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. Syntax SELECT columns FROM table FULL OUTER JOIN table ON table1.column = table2.column; Visual Illustration
- What is subquery? Explain it. A subquery is a query nested inside another query. The query inside the brackets is called a subquery or an inner query. The query that contains the subquery is known as an outer query. PostgreSQL executes the query that contains a subquery in the following sequence:
- First, executes the subquery.
- Second, gets the result and passes it to the outer query.
- Third, executes the outer query. Sub query with IN operator A subquery can return zero or more rows. To use this subquery, you use the IN operator in the WHERE clause. Example
SELECT ename FROM emp where salary IN (SELECT SALARY FROM emp WHERE salary>20000) Sub query with EXISTS operator A subquery can be an input of the EXISTS operator. If the subquery returns any row, the EXISTS operator returns true. If the subquery returns no row, the result of EXISTS operator is false. Sub query with ANY operator ANY operator compares a value to a set of values returned by a subquery. The sub query must return exactly one column. The ANY operator must be preceded by one of the following comparison operators =, <=, >, <, > and <> The ANY operator returns true. if any value of the subquery meets the condition, otherwise, it returns false. Example SELECT ename FROM emp where salary ANY= (SELECT SALARY FROM emp WHERE salary>20000) Sub query with ALL operator ALL operator allows you to query data by comparing a value with a list of values returned by a sub query. The ALL operator must be preceded by a comparison operator such as equal (=), not equal (!=), greater than (>), greater than or equal to (>=), less than (<), and less than or equal to (<=). Example SELECT ename FROM emp where salary >ALL (SELECT SALARY FROM emp WHERE salary>20000)
