[go: up one dir, main page]
Scribd
Upload
30 views4 pages

KMBN 208 (SQL Queries)

Uploaded by

Akash Raj
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
30 views4 pages

KMBN 208 (SQL Queries)

Uploaded by

Akash Raj
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

KMBN 208(MIS)

Unit-3

Database Management System (DBMS): Lab Exercises

1. Creation of Table, View, and Reports:

Creation of Table

A table in a database is a collection of related data held in a structured format within a


database. It consists of columns (fields) and rows (records).

Example: Creating a Table

Suppose we are creating a table Students in a database to store student information.

CREATE TABLE Students (


StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DateOfBirth DATE,
Gender CHAR(1),
Major VARCHAR(100),
GPA DECIMAL(3, 2)
);

Explanation:

 StudentID INT PRIMARY KEY: A unique identifier for each student.


 FirstName VARCHAR(50): The first name of the student, with a maximum length of
50 characters.
 LastName VARCHAR(50): The last name of the student, with a maximum length of 50
characters.
 DateOfBirth DATE: The birth date of the student.
 Gender CHAR(1): The gender of the student ('M' for male, 'F' for female).
 Major VARCHAR(100): The student's major subject.
 GPA DECIMAL(3, 2): The student's Grade Point Average.

Creation of View

A view is a virtual table that provides a way to simplify complex queries, encapsulate data
access, and present data in a specific format.

Example: Creating a View

Suppose we want to create a view to display only the student names and their GPAs.

CREATE VIEW StudentGPA AS


SELECT StudentID, FirstName, LastName, GPA
FROM Students;
Explanation:

 CREATE VIEW StudentGPA AS: Creates a new view named StudentGPA.


 SELECT StudentID, FirstName, LastName, GPA FROM Students;: Specifies the
columns from the Students table that the view will present.

Creation of Reports

Reports are formatted presentations of data extracted from the database. Reports can be
created using various tools and SQL queries to generate useful information.

Example: Creating a Simple Report

Let's create a SQL query to generate a report of students with a GPA above 3.0.

SELECT StudentID, FirstName, LastName, GPA


FROM Students
WHERE GPA > 3.0;

Explanation:

 SELECT StudentID, FirstName, LastName, GPA: Specifies the columns to include


in the report.
 FROM Students WHERE GPA > 3.0;: Filters the students to include only those with a
GPA greater than 3.0.

Basics of SQL and Running Queries

SQL (Structured Query Language) is the standard language for interacting with relational
databases. Below are some basic SQL operations.

Inserting Data into a Table

Example: Inserting Data into the Students Table

INSERT INTO Students (StudentID, FirstName, LastName, DateOfBirth, Gender,


Major, GPA)
VALUES (1, 'John', 'Doe', '2000-01-15', 'M', 'Computer Science', 3.5);

Explanation:

 INSERT INTO Students: Specifies the table to insert data into.


 (StudentID, FirstName, LastName, DateOfBirth, Gender, Major, GPA):
Specifies the columns to insert data into.
 VALUES (1, 'John', 'Doe', '2000-01-15', 'M', 'Computer Science',
3.5);: Specifies the values to insert.

Querying Data from a Table

Example: Selecting All Data from the Students Table


SELECT * FROM Students;

Explanation:

 SELECT *: Selects all columns.


 FROM Students;: Specifies the table to query data from.

Example: Selecting Specific Columns

SELECT FirstName, LastName, Major FROM Students;

Explanation:

 SELECT FirstName, LastName, Major: Selects specific columns.


 FROM Students;: Specifies the table to query data from.

Updating Data in a Table

Example: Updating Data in the Students Table

UPDATE Students
SET Major = 'Data Science', GPA = 3.8
WHERE StudentID = 1;

Explanation:

 UPDATE Students: Specifies the table to update data.


 SET Major = 'Data Science', GPA = 3.8: Specifies the columns and new values.
 WHERE StudentID = 1;: Specifies the condition to identify the row to update.

Deleting Data from a Table

Example: Deleting Data from the Students Table

DELETE FROM Students


WHERE StudentID = 1;

Explanation:

 DELETE FROM Students: Specifies the table to delete data from.


 WHERE StudentID = 1;: Specifies the condition to identify the row to delete.

Examples of Advanced Queries

Joining Tables

Example: Inner Join Between Students and Courses

SELECT Students.StudentID, Students.FirstName, Courses.CourseName


FROM Students
INNER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID
INNER JOIN Courses ON Enrollments.CourseID = Courses.CourseID;

Explanation:

 INNER JOIN: Combines rows from two or more tables based on a related column.
 ON Students.StudentID = Enrollments.StudentID: Specifies the join condition
between Students and Enrollments.
 ON Enrollments.CourseID = Courses.CourseID: Specifies the join condition
between Enrollments and Courses.

Aggregating Data

Example: Calculating the Average GPA

SELECT AVG(GPA) AS AverageGPA


FROM Students;

Explanation:

 SELECT AVG(GPA) AS AverageGPA: Calculates the average GPA and assigns an alias
AverageGPA.
 FROM Students;: Specifies the table to query data from.

You might also like