Worksheet Week4

DBMS Lab Task
WEEK-4 SQL Functions , SET Operators and Integrity Constraints

&DCL, TCL
SQL Numeric Functions

1. abs()
The abs() function returns the absolute (positive) value of a number.
Ex: SELECT ABS(-243.5);
2. ceil()
The CEIL() function returns the smallest integer value that is bigger than or equal to a number.
Ex: SELECT CEIL(25.75);
3. floor()
The FLOOR() function returns the largest integer value that is smaller than or equal to a number.
Ex: SELECT FLOOR(25.75);
4. mod(x,y)
The MOD() function returns the remainder of a number divided by another number.
Ex: SELECT MOD(18, 4);
5. pow(n,p)
The POW() function returns the value of a number raised to the power of another number.
Ex: SELECT POW(4, 2);
6. rand()
The RAND() function returns a random number between 0 (inclusive) and 1 (exclusive).
Ex: SELECT RAND();
7. round()
The ROUND() function rounds a number to a specified number of decimal places.
Ex: SELECT ROUND(135.375, 2);
8. sqrt()
The SQRT() function returns the square root of a number.
Ex: SELECT SQRT(64);
9. sin()
The SIN() function returns the sine of a number.
Ex: SELECT SIN(2);
Dept. of CSE, NIT Andhra Pradesh 1

SQL String Functions
1. concat()
The CONCAT() function adds two or more expressions together.
Ex: SELECT CONCAT("SQL ","is ", "fun!")
2. lcase()
The LCASE() function converts a string to lower-case.
Ex: SELECT LCASE("SQL Tutorial is FUN!");
3. length()
The LENGTH() function returns the length of a string (in bytes).
Ex: SELECT LENGTH("SQL Tutorial")
4. lpad()
The LPAD() function left-pads a string with another string, to a certain length.
Ex: SELECT LPAD("SQL Tutorial", 20, "ABC");
5. rpad()
The RPAD() function right-pads a string with another string, to a certain length.
Ex: SELECT RPAD("SQL Tutorial", 20, "ABC");
6. ltrim()
The LTRIM() function removes leading spaces from a string.
Ex: SELECT LTRIM(" SQL Lab”);
7. repeat(str,number)
The REPEAT() function repeats a string as many times as specified.
Ex: SELECT REPEAT(“SQL Task”, 3);
8. replace()
The REPLACE() function replaces all occurrences of a substring within a string, with a new substring.
Ex: SELECT REPLACE("SQL Tutorial", "SQL", "HTML");
9. ucase()
The UCASE() function converts a string to upper-case.
Ex: SELECT UCASE("SQL Tutorial is FUN!");
10. strcmp()
The STRCMP() function compares two strings
Ex: SELECT STRCMP("SQL Tutorial", "SQL Tutorial");
11.trim()
The TRIM() function removes leading and trailing spaces from a string.
Ex: SELECT TRIM(' SQL Tutorial ')
12.substr(string,start,length)
The SUBSTR() function extracts a substring from a string (starting at any position).
Ex: SELECT SUBSTR("SQL Tutorial", 5, 3)

Type Conversion:
TO_CHAR Function :
TO_CHAR function is used to typecast a numeric or date input to character type with a format
model (optional).
SYNTAX :
TO_CHAR(date, ’format_model’)
Ex: SELECT empid, TO_CHAR(hire_date, ’MM/YY’) Month_Hired FROM employees WHERE
ename = ’Scott’;
Output:
EMPID MONTH_HIRED
205 06/94
Elements of the Date Format Model :

YYYY Year
MM Month
YEAR Year spelled out
DAY Full Name of the week
MONTH Full name of the month
DD Numeric day of the month
SQL Date Functions

1. sysdate(): The SYSDATE() function returns the current date and time.
Ex: SELECT SYSDATE();
2. current_date(): The CURRENT_DATE() function returns the current date.
Ex: SELECT CURRENT_DATE();
3. current_time(): CURRENT_TIME() function returns the current time.
Ex: SELECT CURRENT_TIME();
4. month(): The MONTH() function returns the month part for a given date
Ex: SELECT MONTH("2017-06-15");
5. hour(): The HOUR() function returns the hour part for a given date .
Ex: SELECT HOUR("2017-06-20 09:34:00");

SQL SET Operators:
SET operators are special type of operators which are used to combine the result of two queries. Operators
covered under SET operators are:
1. UNION : UNION will be used to combine the result of two select statements. Duplicates removed.
2. UNION ALL: combines all the records from both the queries. Duplicates not removed.
3. INTERSECT: returns the records which are common from both SELECT statements.
4. MINUS : It displays the rows which are present in the first query but absent in the second query
with no duplicates
There are certain rules which must be followed to perform operations using SET operators in SQL. Rules
are as follows:
1. The number and order of columns must be the same.
2. Data types must be compatible.
Questions:
1. Display employee id, Firstname, LastName from employees of both USA and UK.
2. Display employee id, Firstname, LastName from employees who are working for both countries
US and UK.
3. Display details of Female HR employees of UK who are not working in USA.
4. Display IT employees of USA who are not working in UK.
5. Display HR employees who are working in both countries.
6. Display ALL employees of both Countries including duplicate values.

Integrity Constraints:
Q. Create following tables with Integrity constraints:
Voters(voterid, name, age, mobile, city, Adharno)
Citizens(adharno, name, address)
1. Make adharno and voterid as Primary keys of the relations Citizens and Voters.
2. Add check Constraint for age for Voters table as who is greater than or equal to 18
3. Add check Constraint for mobile no for Voters table its length must be 10 digit mobile number
4. Add check Constraint for adharno for Citizens table its length must be 12 digit adhar number.
5. Add Foreign key city in the Voters table which references address in Citizens table.
6. Add NOT NULL constraint to the name column of the Voters table.
7. Add UNIQUE Constraint to the adharno in the Voters table.
8. Add Foreign key adharno in the Voters table which references adharno in Citizens table.
9. Add DEFAULT Constraint to age column of the Voters as age by default 18.
DCL Commands:
Data Control Language (DCL) is a subset of SQL commands used to control access to data in a database. DCL is
crucial for ensuring security and proper data management, especially in multi-user database environments. The
primary DCL commands in SQL include:
1. GRANT: This command is used to give users access privileges to the database. These privileges
can include the ability to select, insert, update, delete, and so on, over database objects like tables
and views.
• Syntax: GRANT privilege_name ON object_name TO user_name;
• For example, GRANT SELECT ON employees TO user123; gives user123 the
permission to read data from the employees table.
2. REVOKE: This command is used to remove previously granted access privileges from a user.
• Syntax: REVOKE privilege_name ON object_name FROM user_name;
• For example, REVOKE SELECT ON employees FROM user123; would
remove user123‘s permission to read data from the employees table.
DCL commands are typically used by database administrators. When using these commands, it’s important to
carefully manage who has access to what data, especially in environments where data sensitivity and user roles
vary significantly.

Example for reference:
Create user ‘paul@localhost’ identified by ‘dbms@123’; //Creating User
GRANT SELECT, INSERT, DELETE, UPDATE ON Users TO 'Amit'@'localhost'; //Granting Permissions
GRANT ALL ON Users TO 'Amit'@'localhost';
REVOKE SELECT, INSERT, DELETE, UPDATE ON Users FROM 'Amit'@'localhost'; //Revoking Permissions
REVOKE ALL ON Users FROM 'Amit'@'localhost';
Questions: below questions are indicative if you want to try differently you can do it and write the same.
1.Create a user with name as ‘Student’ with own Password
2. Grant Only select, delete, update Privilege on result table for the user student.
3.Grant Select, Insert update Only Privilege on student table for the user.
4.Grant all privilege on dept table for the user.
5. revoke delete, update Privilege on result table for the user student.
TCL Commands:
Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and
ends when all the tasks in the group are successfully completed. If any of the tasks fail, the
transaction fails. Therefore, a transaction has only two results: success or failure. You can explore
more about transactions here. Hence, the following TCL commands are used to control the execution
of a transaction:
BEGIN: Opens a Transaction.
COMMIT: Commits a Transaction.
Syntax:
COMMIT;
ROLLBACK: Rollbacks a transaction in case of any error occurs.
Syntax:
ROLLBACK;
SAVEPOINT: Sets a save point within a transaction.
Syntax:
SAVEPOINT SAVEPOINT_NAME; //create save point
ROLLBACK TO SAVEPOINT_NAME; // use save point
***Apply and try above commands while executing the DDL, DML Commands of your choice and write the
same.***

Worksheet Week4

Uploaded by

Copyright:

Available Formats

Worksheet Week4

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Worksheet Week4

Uploaded by

Copyright:

Available Formats

DBMS Lab Task

WEEK-4 SQL Functions , SET Operators and Integrity Constraints

SQL Numeric Functions

Ex: SELECT CEIL(25.75);

Dept. of CSE, NIT Andhra Pradesh 1

Dept. of CSE, NIT Andhra Pradesh 2

Elements of the Date Format Model :

SQL Date Functions

Dept. of CSE, NIT Andhra Pradesh 3

Dept. of CSE, NIT Andhra Pradesh 4

Dept. of CSE, NIT Andhra Pradesh 5

Dept. of CSE, NIT Andhra Pradesh 6

You might also like