1.
CREATE a TABLE and store 5 student data using sql:
➢ CREATE TABLE Students (
StudentID INT (10) PRIMARY KEY,
FirstName VARCHAR(20),
LastName VARCHAR(20),
DOB DATE
);
Output:-
Inserting Data for 5 Student:-
INSERT INTO Students (StudentID, FirstName, LastName, DOB)
VALUES
(1, 'Srabanti', 'Khamrai', '1950-12-12' ),
(2, 'Ishita', 'Bera', '2008-11-23'),
(3, 'Snehendu', 'Utthasini', '2002-02-15'),
(4, 'Dipanwita', 'Jana', '2000-03-30'),
(5, 'xyz', 'Das', '1998-07-23' );
Show table:
select * from students;
Output:-
2. create a table in SQL and show the details of the
student with Roll No 1:
➢ Create table students (
Roll_no int (10) primary key,
name varchar(50),
age int(10) not null,
gender varchar(10)not null,
course varchar(50)
);
inserting data:-
Insert into students (Roll_no, name, age, gender,
course)values
(1, 'Srabanti Khamrai', 20, 'Female', 'BCA'),
(2, 'Ishita Bera', 21, 'female', 'mathematics'),
(3, 'Snehendu Utthasini', 21, 'male', 'BBA');
Query to Show Roll No 1 Student Details:
SELECT * FROM Students
WHERE Roll_no = 1;
Output:-
3. create a table in sql and show the details of the
student marks with 100 and 200:
➢ Create table students (
Roll_no int (10) primary key,
name varchar(50),
age int(10) not null,
gender varchar(10)not null,
course varchar(50),
Marks INT
);
Insert student data:
Insert into students (Roll_no, name, age, gender, course,
Marks)values
(1, 'Srabanti Khamrai', 20, 'Female', 'BCA',175),
(2, 'Ishita Bera', 21, 'female', 'mathematics',130),
(3, 'Snehendu Utthasini', 21, 'male', 'BBA',165);
Query to Get Student Details with Marks Between 100
and 200:
4. join two tables using SQL, with two tables (Students
and Courses):
STUDENTS TABLE CREATED:
➢ Create table students (
Roll_no int (10) primary key,
name varchar(50),
age int(10) not null,
gender varchar(10)not null,
course varchar(50),
Course_id INT
);
CREATE TABLE COURSE:
CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(100),
Instructor VARCHAR(100)
);
Insert Data into STUDENT Tables:
INSERT INTO Students (RollNo, Name, Age, Gender, CourseID)
VALUES
(1, 'Srabanti', 20, 'Female', 101),
(2, 'GGSWHW', 22, 'Female', 102),
(3, 'GHHSSH', 21, 'Female', 103),
(4, 'Ishita', 23, 'FeMale', 101);
Insert Data into COURSE Tables:
INSERT INTO Courses (CourseID, CourseName, Instructor)
VALUES
(101, 'Computer Science', 'Dr. Smith'),
(102, 'Mathematics', 'Dr. Johnson'),
(103, 'Physics', 'Dr. Williams');
Perform a Join:
SELECT Students.RollNo, Students.Name, Students.Age,
Students.Gender, Courses.CourseName,
Courses.Instructor
FROM Students
JOIN Courses ON Students.CourseID = Courses.CourseID;
5. write a sql quary find avg salary for employee:
Create the Employees Table:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
Age INT,
Gender VARCHAR(10),
Salary INT
);
Insert Sample Data into the Employees Table:
INSERT INTO Employees (EmployeeID, Name, Age,
Gender, Salary)VALUES
(1, 'John Doe', 30, 'Male', 50000),
(2, 'Jane Smith', 28, 'Female', 60000),
(3, 'Alice Johnson', 35, 'Female', 55000),
(4, 'Bob Brown', 40, 'Male', 70000),
(5, 'Charlie White', 25, 'Male', 45000);
SQL Query to Find the Average Salary:
SELECT AVG(Salary) AS AverageSalary
FROM Employees;
Output:-
