DBMS LAB@Reshma Rijo Experiment No: 2 Creation, modification, configuration, and deletion of databases Commands AIM: Creation ofa database and tables using DDL commands COMMANDS Create Database mysql> create database testdb; Query OK, 1 row affected (0.01 sec) Use Database created mysql> use testdb; Database changed Create Table Mysql> create table student (stname varchar(30), stid varchar(10), stage int(2), starea varchar(20)): Query OK, 0 rows affected (0.34 sec) Description of student dese student; |Field | Type | Null | Key | Default | Extra | + ra | stname | varchar(30)|YES | |NULL | | |stid | varchar(10)|YES | |NULL | | stage |int(2) |YES | |NULL | | | starea | varchar(20)|YES | |NULL | | tee — a 4 rows in set (00.01 sec) MODIFY TABLE DESCRIPTION alter table student modify stage int(5);DBMS LAB@Reshma Rijo Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 dese student; | Field | Type [Null | Key | Default | Extra | | stname | varchar(30)|YES | |NULL | | |stid | varchar(10)|YES | [NULL | | |stage |int(S) | YES | | NULI | starea | varchar(20) | YES |_| NUL | stdept]| varchar(20)|YES | [NULL | | Sows inset (0.00 See) alter table student drop stdept; Query OK, 0 rows affected (0.55 see) Records: 0 Duplicates: 0 Warnings: 0 dese student; mysql> desc student, __ ae | Null | Key | Default | Extra | +. + a | Field | Type fee | stname | varchar(30)| YES | | NULL | | |stid | varchar(10)|YES | |NULL | | stage |in(S) |YES| |NULL | | | starea | varchar(20)| YES | |NULL | | tt +. 4 rows in set (0.00 sec) CLEAR ALL VALUES IN TABLE truncate table student; Query OK, 0 rows affected (0.25 sec) mysql> dese student;DBMS LAB@Reshma Rijo + + | stname | varchar(30)| YES | |NULL | |stid | varchar(10)|YES | |NULL | |stage |in(S) |YES | |NULL | | starea | varchar(20) | YES | | NULL +. +-. nennnnn=t- 4 rows in set (0.00 sec) DELETE TABLE BOTH SCHEMA AND DATA. drop table student; Query OK, 0 rows affected (0.18 sec) mysql> dese student; ERROR 1146 (42802): Table 'testdb.student’ doesn't exist DELETE DATABASE mysql> DROP DATABASE databasename; Database dropped RESULT Query has run successfully and result is obtained. By constructing queries using SQL I was able to identify the queries for dealing with database activities.Ex.No:3 To practice and implement data definition language commands and constrains AIM Creation of database schema - DDL (create tables, set constraints, enforce relationships, create indices, delete and modify tables). Export ER diagram from the database and verify relationships** Consider the employee database given below emp (emp_idemp_name, Street_No, city) works (emp_id. company name, salary) company (company name, city) manages (emp_id, manager_id) Note: Emp_id should start with “E’ in Emp table and emp_id in works table must be the emp_id from emp table . emp_id and manager _id in manages table must be the emp_id from emp table 1 Add these four tables with sufficient constraints. 1 Alter table emp add a constraint that emp_name cannot be null. COMMANDS, 1 A)Create table emp Create table emp(emp_id char() check(emp_id like "E%') primary key, emp_name varchar(18),street_no int.city varchar(18)); B) Create table company Create table company(company name varchar(18) primary key, city varchar(18)); Create table works Create table works(emp_id —char(8) references emp(emp_id),company name varchar(18) _ references company(company_name),salary float,primary key(emp_id,company_name)); ») Create table manages Create table manages(emp_id char(8) _ references, emp2(emp_id),manager_id char(8)_—_—_—references, emp2(emp_id),unique(emp_id,manager_id)); u.Alter table emp alter table emp MODIFY emp_name varchar(18) NOT NULL; RESULT Query has run successfully and result is obtained. By constructing queries using SQL I was able to identify the queries for dealing with database activitiesEx.No:4 Database initialization - Data insert, Data import to a database (bulk import using UI and SQL Commands) * AIM To insert data to tables used in experiment no 3 using insert commands and bulk import using UI and sql commands COMMANDS, 1. INSERT COMMANDS, insert into emp values(*E-101’,Adarsh’,101,'MG Road’); insert into emp values(*E-102",Bonny',101,'MG Road’); insert into emp values(*E-103°/Catherine’, 102, ‘Cochin’; insert into emp values(*E-104’,Glenn’, 104, 'Emakulam’); insert into emp values(*E-105°/George’, 201,!MG. Road’); insert into emp values(‘E-106’, Hayes’, 101, 'MG Road’); insert into emp values(*E-107",Johnson’,102,’Cochin’); insert into emp values(*E-108",Jones', 101, 'Cochin’); insert into emp values(*E-109°,*Karthik’, 101, ‘Emakulam’); insert into emp values(*E-110",'Lavanya', 101, 'Palace Road’); insert into emp values(*E-IL1’,Niharika’, 102, *Emakulam’); insert into company values(’SBI, 'MG Road’); insert into company values(‘SBT, 'MG Road’ ); insert into company values(’Federal’,’ Broadway’); insert into company values(‘Indian Bank’, ‘Cochin’); insert into company values('SIB', Emakulam’); insert into company values(‘HDFC’, ‘Palace Road’); insert into company values(‘Axis’,’Cochin’); insert into company values(City bank’, 'Emakulam’); insert into works values('E-101', 'SBI', 71000); insert into works values('E-102', 'SBI', 90000); insert into works values('E-103', 'SBT', 40000); insert into works values(*E-104°, 'Federal’, 37000); insert into works values('E-105', ‘SBT’, 17000) insert into works values('E-106, ‘Indian Bank’, 30000); insert into works values('E-107', ‘SIB’, 21000); insert into works values(*E-108', ‘SIB', 18000); insert into works values(*E-109°, "Indian Bank’, 28000); insert into works values(*E-110°, ‘SBT’, 250000); insert into works values(*E-111", 'Federal’, 40000);insert into manages values’ insert into manages valuesE insert into manages values('E insert into manages values E insert into manages values(" insert into manages values("t insert into manages valuesE insert into manages values("E-111°, null); RESULT Query has run successfully and result is obtained. By constructing queries using SQL I was able to identify the queries for dealing with database activities.Ex. No:5 Practice SQL commands for DML (insertion, updating, altering, deletion of data, and viewing/querying records based on condition in databases) AIM Consider the employee database created in Find results for the following questions a. Find the names of all employees who work for SBI. b. Find all employees in the database who live in the same cities as the companies for which they work. c. Find all employees and their managers in the database who live in the same cities and on the same street number as do their managers. d. Find all employees who eam more than the average salary of all employees of their company. . Find the company that pay least total salary along with the salary paid. £ Give all managers of SBI a 10 percent raise. g. Find the company that has the most employees h. Find those companies whose employees earn higher salary, on average than the average salary at Indian Bank i. Query to find name and salary of all employees who earn more than each employee of ‘Indian Bank” COMMANDS, a) Find the names of all employees who work for SBI. SELECT emp_name FROM works,emp WHERE company_name="SBI" and emp.emp_id=works.emp_id; EMP_NAME Adarsh b) Find all employees in the database who live in the same cities as the companies for which they work. SELECT emp.emp_name FROM emp, works,company WHERE ‘emp.emp_id = works. emp_id AND works. company_name= company.company_name AND emp city = company.city EMP_NAME, Adarsh. Georgec) Find all employees and their managers in the database who live in the same cities and on the same street number as do their managers. ELECT emp.emp_name,e2.emp_name “manager name" FROM emp.emp e2, manages WHERE emp.emp i AND 2.Emp_i manages.manager_id AND emp.street_no = e2.street_no AND emp.city = e2.city manages.emp_i EMP_NAME manager name Adarsh Bonny d) Find all employees who eam more than the average salary of all employees of their company. SELECT emp_name,emp.emp_id,salary FROM works emp WHERE salary > (SELECT AVG (salary) FROM works S WHERE works.company_name =S.company_name) and emp.emp_id=works.emp_id EMP_NAME EMP_ID SALARY Bonny E-102 90000 Hayes E-106 30000 Johnson, E-107 21000 Lavanya 110 230000 Niharika E-HL 40000 e). Find the company that pay least total salary along with the salary paid. SELECT company_name,sum(salary) “SALARY PAID" from Works GROUP BY company_name HAVING sum(salary) <= all (SELECT sum(salary) FROM Works GROUP BY company_name) COMPANY_NAME SALARY PAID sIB 39000 £) Give all managers of SBI a 10 percent raise UPDATE works SET salary = salary * 1.1 WHERE emp_id in (select manager_id from manages) and company_name ~ SBT’; g). Find the company that has the most employees SELECT company_name FROM works GROUP BY company_name HAVING COUNT (DISTINCT emp_id) >= ALL (SELECT COUNT (DISTINCT emp_id) FROM works GROUP BY company_name) COMPANY_NAME,h) Find those companies whose employees earn a higher salary, on average than the average salary at Indian Bank. SELECT company_name FROM works GROUP BY company_name HAVING AVG(salary)> (SELECT AVG(salary) FROM works WHERE company_name = ‘Indian Bank’ GROUP BY company_name) COMPANY _NAME SBI Federal SBT i).Query to find name and salary of all employees who earn more than each employee of ‘Indian Bank’ SELECT emp_name,salary FROM works,emp WHERE salary > (SELECT MAX(salary) FROM works WHERE company_name = ‘Indian Bank’ GROUP BY company_name) and emp.emp_id=works.emp_id; EMP_NAME SALARY Adarsh 71000 Bonny 99000 Catherine 40000 Glenn 37000 Lavanya 250000 Niharika 40000Ex. No: 6 Implementation of built-in functions in RDBMS AIM RDBMS Built in Functions There are two types of functions: 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 data based on the values returned by the query. This is discussed in SQL GROUP Functions. The group functions are used to calculate aggregate values like total or average, which return just one total or one average value after processing a group of rows. There are four types of single row funetions. They are: ») Numeric Functions: These are functions that accept numeric input and return numeric values. 2) Character or Text Funetions: These are functions that accept character input and can return both character and number values. 1») Date Funetions: These are functions that take values that are of datatype DATE as input and return values of datatype DATE, except for the MONTHS_BETWEEN function, which returns a number. 4) Conversion Functions: These are functions that help us to convert a value in one form to another form. For Example: a null value into an actual value, or a value from one datatype to another datatype like NVL, TO_CHAR, TO_NUMBER, TO_DATE etc. L.Mathematical Functions A) SQL> select ABS(-100) from dual; 100 B)SQL* select ABS(-6) from dual; C)SQL> select FLOOR(2345.78) FROM DUAL; 2345 D)SQL> SELECT GREATEST(23,67,90,123,78,50) FROM DUAL; 123 E)SQL> SELECT LEAST(34, 21,67,11,89,9) FROM DUAL; 9 F)SQL> SELECT LENGTH(RAJESHWARI') FROM DUAL;F)SQL> SELECT LENGTH(RAJESHWARI') FROM DUAL; 10 G)SQL> SELECT LENGTH(17245637) FROM DUAL; H)SQL> SELECT SQRT(16) FROM DUAL; I)SQL> SELECT SQRT(99) FROM DUAL; 9.94987437 JSQL> SELECT POWER(2,4) FROM DUAL; 1024 L)SQL> SELECT power(2,10) FROM DUAL; 1024 M) SQL> SELECT ROUND(5.86) FROM DUAL; N)SQL> SELECT ROUND(1001.6) FROM DUAL; 1002 O)SQL> SELECT ROUND(1001.3) FROM DUAL; 1001 P)SQL> SELECT SIN(90) FROM DUAL; 893996664 Q)SQL> SELECT COS(45) FROM DUAL, 525321989 R)SQL> SELECT TAN(30) FROM DUAL; ~6.4053312 S)SQL> SELECT TAN(90) FROM DUAL; -1.9952004T)SQL> SELECT TAN(180) FROM DUAL; 1.33869021 U)SQL> SELECT SIGN(-128) FROM DUAL; f V)SQL> SELECT SIGN(10) FROM DUAL; W)SQL> SELECT SIGN(0) FROM DUAL; 0 X)SQL> SELECT LN(100) FROM DUAL; 4,60517019 Y)SQL> SELECT LN(10) FROM DUAL; 2.30258509 Z)SQL> SELECT LOG(10,100) FROM DUAL; 2 AA)SQL> SELECT LOG(100,10) FROM DUAL; J AB)SQL> SELECT MOD(4,3) FROM DUAL; 1 AC)SQL> SELECT MOD(4,2) FROM DUAL; 0 AD)SQL> SELECT EXP(2) FROM DUAL; 7.3890561 AE)SQL> SELECT EXP(-2) FROM DUAL; 135335283 AF)SQL> SELECT EXP(0) FROM DUAL;2)Date Functions A)SQL> SELECT CURRENT_DATE FROM DUAL; CURRENT_D 14-AUG-25 B)SQL> SELECT EXTRACT(YEAR FROM SYSDATE()) EXTRACT(YEARFROMSYSDATE) CT EXTRACT(DAY FROM SYSDATE() ; EXTRACT(DAYFROMSYSDATE) 14 D)SQL> SELECT EXTRACT(MONTH FROM SYSDATE()) ; EXTRACT(MONTHFROMSYSDATE) 8 E)SQL> SELECT SYSDATE(); SYSDATE AUG-19 3)String Functions A)SQL> select ascii('t) from dual; ASCIIC(T) 116 B)SQL> select ascii('a') from dual; ASCIICA') 97 C)SQL> select ascii('A’) from dual; ASCIICA') 65 D)SQL> select ascii('Z') from dual; ASCIICZ’) 90 E)SQL> select ascii('z’) from dual; ASCII(Z') 122 F)SQL> SELECT UPPER(‘bldea sb arts and kep science college’) from dual; UPPER('BLDEASBARTSANDKCPSCIENCECOLLEG’) BLDEA SB ARTS AND KCP SCIENCE COLLEGE G)SQL> select LOWER(welcome to dbms lab’) from dual; LOWER('WELCOMETODBM welcome to dbms labH)SQL> select LOWER(WELCOME TO DBMSLAB') from dual; LOWER('WELCOMETODB welcome to dbmslab I)SQL> SELECT REPLACE('HELLO',H','K') FROM DUAL; REPLA KELLO JJSQL> SELECT REPLACE('COMPUTER DUAL; REPLACE( KOMPUTER K)SQL> SELECT REPLACE('HELLO''L''A') FROM DUAL; REPLA HEAAO. L)SQL> SELECT TRIM('A' FROM 'ANACONDA') FROM DUAL; TRIM( ,'K') FROM NACOND M)SQL> SELECT TRIM(LEADING ‘A’ FROM '"ANACONDA)); NACONDA N)SQL> SELECT TRIM(LEADING ‘A’ FROM 'ANIL’); NIL O)SQL> SELECT TRIM(TRAILING ‘A’ FROM 'ANITA'); ANIT P)SQL> SELECT TRIM(TRAILING ‘A’ FROM "ANACONDA’); ANACOND Q)SQL> SELECT TRIM(TRAILING ‘A’ FROM "ANACONDA ') ;