A

LAB REPORT

ON

DATABASE MANAGEMENT SYSTEM

By

Jenish Pokhrel

Exam Roll No: 8634/18

Submitted to:

Harendra Subedi

IT Department

Kantipur College of Management and Information Technology

In partial fulfillment of the requirements for the Course

Database Mangement System

Mid Baneshwor, Kathmandu

February 2021
Table of Contents
1. SQL code to create a database............................................................................................................6
1.1 Source Code.................................................................................................................................6
1.2 Output.........................................................................................................................................6
2. SQL code to create a table...................................................................................................................6
2.1 Source Code.................................................................................................................................6
2.2 Output.........................................................................................................................................7
3. SQL code to insert data into table.......................................................................................................7
3.1 Source Code.................................................................................................................................7
3.2 Output.........................................................................................................................................7
4. SQL code to update from table............................................................................................................7
4.1 Source Code.................................................................................................................................7
4.2 Output.........................................................................................................................................8
5. SQL code to to delete data from table.................................................................................................8
5.1 Source Code.................................................................................................................................8
5.2 Output.........................................................................................................................................8
6. SQL code to return average value........................................................................................................8
6.1 Source Code.................................................................................................................................8
6.2 Output.........................................................................................................................................9
7. SQL code to return minimum value.....................................................................................................9
7.1 Source Code.................................................................................................................................9
7.2 Output.........................................................................................................................................9
8. SQL code to return maximum value....................................................................................................9
8.1 Source Code.................................................................................................................................9
8.2 Output.........................................................................................................................................9
9. SQL code to return sum.....................................................................................................................10
9.1 Source Code...............................................................................................................................10
9.2 Output.......................................................................................................................................10
10. SQL code to count total number of tuple in a specified rows........................................................10
10.1 Source Code...............................................................................................................................10
 10.2 Output.......................................................................................................................................10
11. SQL code showing the use of distinct keyword..............................................................................10
11.1 Source Code...............................................................................................................................10
11.2 Output.......................................................................................................................................11
12. SQL code using LIKE clause............................................................................................................11
12.1 Source Code...............................................................................................................................11
12.2 Output.......................................................................................................................................11
13. SQL code using NOT LIKE clause....................................................................................................11
13.1 Source Code...............................................................................................................................11
13.2 Output.......................................................................................................................................12
14. SQL code to give an alias name to a column..................................................................................12
14.1 Source Code...............................................................................................................................12
14.2 Output.......................................................................................................................................12
15. SQL code to create a virtual table..................................................................................................12
15.1 Source Code...............................................................................................................................12
15.2 Output.......................................................................................................................................12
16. SQL code to update view...............................................................................................................13
16.1 Source Code...............................................................................................................................13
16.2 Output.......................................................................................................................................13
17. SQL code to delete a view..............................................................................................................13
17.1 Source Code...............................................................................................................................13
17.2 Output.......................................................................................................................................13
18. SQL code to concat data from table..............................................................................................13
18.1 Source Code...............................................................................................................................13
18.2 Output.......................................................................................................................................14
19. SQL code to show first three character..........................................................................................14
19.1 Source Code...............................................................................................................................14
19.2 Output.......................................................................................................................................14
20. SQL code to show last three character..........................................................................................14
20.1 Source Code...............................................................................................................................14
20.2 Output.......................................................................................................................................15
21. SQL code to reverse the string.......................................................................................................15
 21.1 Source Code...............................................................................................................................15
21.2 Output.......................................................................................................................................15
22. SQL code to display substring........................................................................................................15
22.1 Source Code...............................................................................................................................15
22.2 Output.......................................................................................................................................16
23. SQL code to show ASCII value........................................................................................................16
23.1 Source Code...............................................................................................................................16
23.2 Output.......................................................................................................................................16
24. SQL code to arrange the data in descending order........................................................................16
24.1 Source Code...............................................................................................................................16
24.2 Output.......................................................................................................................................17
25. SQL code to arrange the data in table...........................................................................................17
25.1 Source Code...............................................................................................................................17
26. SQL code showing the use of Group by Clause..............................................................................17
26.1 Source Code...............................................................................................................................17
26.2 Output.......................................................................................................................................18
27. SQL code using Having Clause........................................................................................................18
27.1 Source Code...............................................................................................................................18
27.2 Output.......................................................................................................................................18
28. SQL code showing how NOT NULL works......................................................................................18
28.1 Source Code...............................................................................................................................18
28.2 Output.......................................................................................................................................19
29. SQL code showing Unique Constraint............................................................................................19
29.1 Source Code...............................................................................................................................19
29.2 Output.......................................................................................................................................20
30. SQL code to show Foreign Key.......................................................................................................20
30.1 Source Code...............................................................................................................................20
30.2 Output.......................................................................................................................................20
31. SQL code to add CHECK constraints...............................................................................................20
31.1 Source Code...............................................................................................................................20
31.2 Output.......................................................................................................................................21
32. SQL code to add DEFAULT constraints...........................................................................................22
 32.1 Source Code...............................................................................................................................22
32.2 Output.......................................................................................................................................22
33. SQL to use Between operator........................................................................................................22
33.1 Source Code...............................................................................................................................22
33.2 Output.......................................................................................................................................22
34. SQL code to use IN operator..........................................................................................................23
34.1 Source Code...............................................................................................................................23
34.2 Output.......................................................................................................................................23
35. SQL code showing order by clause................................................................................................23
35.1 Source Code...............................................................................................................................23
35.2 Output.......................................................................................................................................23
36. SQL code to use INNER JOIN..........................................................................................................23
36.1 Source Code...............................................................................................................................23
36.2 Output.......................................................................................................................................23
37. SQL code to use LEFT OUTER JOIN.................................................................................................24
37.1 Source Code...............................................................................................................................24
37.2 Output.......................................................................................................................................24
38. SQL code to use RIGHT OUTER JOIN..............................................................................................24
38.1 Source Code...............................................................................................................................24
38.2 Output.......................................................................................................................................24
39. SQL code to use subquery.............................................................................................................24
39.1 Source Code...............................................................................................................................24
39.2 Output.......................................................................................................................................24
40. SQL code to use UNION clause......................................................................................................25
40.1 Source Code...............................................................................................................................25
40.2 Output.......................................................................................................................................25
41. SQL code to use UNION ALL clause................................................................................................26
41.1 Source Code...............................................................................................................................26
41.2 Output.......................................................................................................................................26
42. SQL code to use INTERSECT clause................................................................................................26
42.1 Source Code...............................................................................................................................26
42.2 Output.......................................................................................................................................27
43. SQL code to use EXCEPT clause......................................................................................................27
43.1 Source Code...............................................................................................................................27
43.2 Output.......................................................................................................................................27
44. SQL code to TRUNCATE a table......................................................................................................28
44.1 Source Code...............................................................................................................................28
44.2 Output.......................................................................................................................................28
45. SQL code using COMMIT command...............................................................................................28
45.1 Source Code...............................................................................................................................29
45.2 Output.......................................................................................................................................29
1. SQL code to create a database.
1.1 Source Code
CREATE DATABASE jenish_p;

1.2 Output

2. SQL code to create a table.

2.1 Source Code
USE jenish_p;

CREATE TABLE person(

s_id INT PRIMARY KEY,

F_NAME VARCHAR(30),

L_Name VARCHAR(30),

phone LONG,

email VARCHAR(30)

);

SELECT * FROM person;

2.2 Output

3. SQL code to insert data into table.

3.1 Source Code
INSERT INTO person(s_id,F_Name,L_Name,phone,email) VALUE
('9','jenish','POKHREL','9816766390','[email protected]');

SELECT * FROM person;

3.2 Output

4. SQL code to update from table.

4.1 Source Code
alter table person add(Address varchar(30))

UPDATE person

SET F_Name='JENISH' , Address='Kathmandu' WHERE s_id='9';

SELECT * FROM person;

4.2 Output

5. SQL code to to delete data from table

5.1 Source Code
DELETE FROM person

SELECT * FROM person;

5.2 Output

6. SQL code to return average value.

6.1 Source Code

SELECT AVG(Salary) FROM person WHERE Address='Kathmandu';
6.2 Output

7. SQL code to return minimum value.

7.1 Source Code
SELECT min(Salary) FROM person WHERE Address='Kathmandu';

7.2 Output

8. SQL code to return maximum value.

8.1 Source Code
SELECT MAX(Salary) FROM person WHERE Address='Kathmandu';

8.2 Output
9. SQL code to return sum.
9.1 Source Code
SELECT sum(Salary) FROM person WHERE Address='Kathmandu';

9.2 Output

10. SQL code to count total number of tuple in a specified rows.

10.1 Source Code
SELECT COUNT(s_id) FROM person WHERE Salary='60000';

10.2 Output

11. SQL code showing the use of distinct keyword.

11.1 Source Code
SELECT DISTINCT F_Name FROM person;
11.2 Output

12. SQL code using LIKE clause.

12.1 Source Code
SELECT * FROM person

WHERE Address LIKE '%ur%';

12.2 Output

13. SQL code using NOT LIKE clause.

13.1 Source Code
SELECT * FROM person

WHERE Address NOT LIKE '%ur%';

13.2 Output

14. SQL code to give an alias name to a column.

14.1 Source Code
SELECT email AS "EMAIL" FROM person;

14.2 Output

15. SQL code to create a virtual table.

15.1 Source Code
CREATE VIEW spc AS SELECT * FROM person WHERE s_id = '68';

SELECT * FROM spc;

15.2 Output
16. SQL code to update view.
16.1 Source Code
update spc set Address='Babarmahal';

SELECT * FROM spc;

16.2 Output

17. SQL code to delete a view.

17.1 Source Code
DROP VIEW spc;

SELECT * FROM spc;

17.2 Output

18. SQL code to concat data from table.

18.1 Source Code
SELECT CONCAT(F_Name,' ',L_Name,' , ',Address) FROM person;
18.2 Output

19. SQL code to show first three character.

19.1 Source Code
SELECT LEFT(F_Name,3) FROM person;

19.2 Output

20. SQL code to show last three character.

20.1 Source Code
SELECT RIGHT (L_Name ,3) FROM person;
20.2 Output

21. SQL code to reverse the string.

21.1 Source Code
SELECT REVERSE (Address) FROM person;

21.2 Output

22. SQL code to display substring.

22.1 Source Code
SELECT substring(L_Name,1,2),L_Name from person;
22.2 Output

23. SQL code to show ASCII value.

23.1 Source Code
SELECT ASCII(Address),Address FROM person;

23.2 Output

24. SQL code to arrange the data in descending order.

24.1 Source Code
SELECT sname FROM student ORDER BY sname DESC;
24.2 Output

25. SQL code to arrange the data in table.

25.1 Source Code
SELECT F_Name FROM person ORDER BY F_Name ASC;

26. SQL code showing the use of Group by Clause.

26.1 Source Code
Select count(s_id), Address from person group by Address;
26.2 Output

27. SQL code using Having Clause.

27.1 Source Code
SELECT * FROM person

GROUP BY Salary

HAVING (Salary) >= 70000;

27.2 Output

28. SQL code showing how NOT NULL works.

28.1 Source Code
ALTER TABLE person ADD Age INT NOT NULL;

SELECT * FROM person;

28.2 Output

29. SQL code showing Unique Constraint.

29.1 Source Code
CREATE TABLE datarecordf (

ID INT NOT NULL,

L_Name VARCHAR(255) NOT NULL,

F_Name VARCHAR(255),

Age INT,

CONSTRAINT UC_Person UNIQUE (ID,L_Name)

);

INSERT INTO datarecordf(ID,L_Name,F_Name,Age) VALUE ('9','POKHREL','JENISH','21');

INSERT INTO datarecordf(ID,L_Name,F_Name,Age) VALUE ('29','HOTCHNER','AARON','50');

SELECT * FROM datarecordf;

29.2 Output

30. SQL code to show Foreign Key.

30.1 Source Code
CREATE TABLE personal (

ID INT NOT NULL,

F_Name INT NOT NULL,

s_id INT,

PRIMARY KEY (ID),

FOREIGN KEY (s_id) REFERENCES person(s_id)

);

SELECT * FROM personal;

30.2 Output

31. SQL code to add CHECK constraints.

31.1 Source Code
CREATE TABLE person(
s_id INT PRIMARY KEY,

F_NAME VARCHAR(30),

L_Name VARCHAR(30),

phone LONG,

email VARCHAR(30)

Salary INT

Age INT NOT NULL CHECK (Age>=21)

);

INSERT INTO person(s_id,F_Name,L_Name,phone,email,Salary,Age) VALUE

('9','jenish','POKHREL','9816766390','[email protected]','70000','21');

INSERT INTO person(s_id,F_Name,L_Name,phone,email,Address,Salary,Age) VALUE

('29','AARON','HOTCHNER','9813924789','[email protected]','Kathmandu','90000','25');

INSERT INTO person(s_id,F_Name,L_Name,phone,email,Address,Salary,Age) VALUE

('45','JENNIFER','JAREAU','9846119834','[email protected]','Bhaktapur','60000','28');

INSERT INTO person(s_id,F_Name,L_Name,phone,email,Address,Salary,Age) VALUE

('68','SPENCER','REID','9829817298','[email protected]','Lalitpur','60000','23');

INSERT INTO person(s_id,F_Name,L_Name,phone,email,Address,Salary,Age) VALUE

('77','EMILY','PRENTISS','9847015291','[email protected]','Anamnagar','50000','20');

SELECT * FROM person;

31.2 Output
32. SQL code to add DEFAULT constraints.
32.1 Source Code
CREATE TABLE datarecordff(

ID INT NOT NULL,

L_Name VARCHAR(255) NOT NULL,

F_Name VARCHAR(255),

Age INT,

Gender VARCHAR(1) DEFAULT 'M'

);

INSERT INTO datarecordff(ID,F_Name,L_Name,Age) VALUE ('9','JENISH','POKHREL','21');

INSERT INTO datarecordff(ID,F_Name,L_Name,Age) VALUE ('29','AARON','HOTCHNER','50');

SELECT * FROM datarecordff;

32.2 Output

33. SQL to use Between operator.

33.1 Source Code
SELECT * FROM person WHERE Salary BETWEEN 50000 AND 60000;

33.2 Output
34. SQL code to use IN operator.
34.1 Source Code
SELECT * FROM person WHERE Address IN('Kathmandu');

34.2 Output

35. SQL code showing order by clause.

35.1 Source Code
SELECT * FROM person ORDER BY SALARY ASC;

35.2 Output

36. SQL code to use INNER JOIN.

36.1 Source Code
SELECT * FROM person INNER JOIN datarecordff WHERE person.s_id = datarecordff.Gender;

36.2 Output
37. SQL code to use LEFT OUTER JOIN.
37.1 Source Code
SELECT * FROM person LEFT JOIN datarecordf2 ON person.Age = datarecordf2.ID;

37.2 Output

38. SQL code to use RIGHT OUTER JOIN.

38.1 Source Code
SELECT * FROM person RIGHT JOIN datarecordf2 ON person.Age = datarecordf2.ID;

38.2 Output

39. SQL code to use subquery.

39.1 Source Code
SELECT * FROM person WHERE Salary=(SELECT MAX(Salary) FROM person);

39.2 Output
40. SQL code to use UNION clause.

40.1 Source Code

SELECT F_Name FROM person

UNION

SELECT F_name FROM datarecordf4

ORDER BY F_Name;

40.2 Output
41. SQL code to use UNION ALL clause.
41.1 Source Code
SELECT F_Name FROM person

UNION ALL

SELECT F_name FROM datarecordf4

ORDER BY F_Name;

41.2 Output

42. SQL code to use INTERSECT clause.

42.1 Source Code
SELECT F_Name FROM person

INTERSECT

SELECT F_name FROM datarecordf4

ORDER BY F_Name;
42.2 Output

43. SQL code to use EXCEPT clause.

43.1 Source Code
SELECT F_Name FROM person

EXCEPT

SELECT F_name FROM datarecordf4

ORDER BY F_Name;

43.2 Output
44. SQL code to TRUNCATE a table.

44.1 Source Code

TRUNCATE TABLE datarecordf4;

44.2 Output

45. SQL code using COMMIT command.

45.1 Source Code
DELETE FROM datarecordf4

WHERE AGE = 28;

COMMIT;

45.2 Output