Scribd
Upload
3 views3 pages

SQL Lab: Students Table Operations

The document outlines SQL commands for creating and managing a 'Students' table, including data insertion, altering the table to add an 'Age' column, and updating age records. It also demonstrates various SQL functions such as mathematical, text, aggregate, and date functions to manipulate and retrieve data from the table. Key operations include rounding marks, counting students, and extracting date information.

Uploaded by

muthulakshmi
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
3 views3 pages

SQL Lab: Students Table Operations

The document outlines SQL commands for creating and managing a 'Students' table, including data insertion, altering the table to add an 'Age' column, and updating age records. It also demonstrates various SQL functions such as mathematical, text, aggregate, and date functions to manipulate and retrieve data from the table. Key operations include rounding marks, counting students, and extracting date information.

Uploaded by

muthulakshmi
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

SQL Lab Exercise – Students Table

Table: Students
Columns: StudentID, Name, Marks, DOB, Age

1. Table Creation and Data Insertion

-- Create table
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Marks DECIMAL(5,2),
DOB DATE
);

-- Insert data
INSERT INTO Students (StudentID, Name, Marks, DOB) VALUES
(1, 'Alice ', 85.25, '2008-05-12'),
(2, ' Bob', 92.50, '2007-11-23'),
(3, 'Charlie', 78.75, '2008-03-04'),
(4, 'David ', 88.10, '2007-07-30');

2. Alter Table

-- Add Age column


ALTER TABLE Students ADD COLUMN Age INT;

3. Update Records

-- Update Age values


UPDATE Students SET Age = 16 WHERE StudentID = 1;
UPDATE Students SET Age = 17 WHERE StudentID = 2;
UPDATE Students SET Age = 16 WHERE StudentID = 3;
UPDATE Students SET Age = 17 WHERE StudentID = 4;

1
4. Mathematical Functions

-- Round Marks to nearest whole number


SELECT Name, Marks, ROUND(Marks) AS RoundedMarks FROM Students;

-- Round Marks to 1 decimal place


SELECT Name, Marks, ROUND(Marks,1) AS RoundedOneDecimal FROM Students;

-- Remainder when Marks divided by 5


SELECT Name, Marks, MOD(Marks,5) AS Remainder FROM Students;

-- Marks squared
SELECT Name, Marks, POWER(Marks,2) AS MarksSquared FROM Students;

5. Text Functions

-- Length of Name
SELECT Name, LENGTH(Name) AS NameLength FROM Students;

-- First 3 letters of Name


SELECT Name, SUBSTRING(Name,1,3) AS FirstThreeLetters FROM Students;

-- Trim functions
SELECT Name, TRIM(Name) AS TrimmedName, LTRIM(Name) AS LeftTrimmedName,
RTRIM(Name) AS RightTrimmedName FROM Students;

-- Find position of 'a'


SELECT Name, INSTR(Name, 'a') AS PositionOfA FROM Students;

-- Upper and Lower case


SELECT Name, UPPER(Name) AS UpperCaseName, LOWER(Name) AS LowerCaseName FROM
Students;

6. Aggregate Functions

-- Total marks
SELECT SUM(Marks) AS TotalMarks FROM Students;

-- Average marks
SELECT AVG(Marks) AS AverageMarks FROM Students;

2
-- Count students
SELECT COUNT(*) AS TotalStudents FROM Students;

-- Minimum and Maximum marks


SELECT MIN(Marks) AS MinMarks, MAX(Marks) AS MaxMarks FROM Students;

7. Date Functions

-- Current date and time


SELECT NOW() AS CurrentDateTime;

-- Day name and Month name of DOB


SELECT Name, DAYNAME(DOB) AS DayOfBirth, MONTHNAME(DOB) AS MonthOfBirth FROM
Students;

You might also like