Munisekhar Udavalapati1.To create class table
CREATE TABLE class(
class_id INT PRIMARY KEY,
class_name VARCHAR(50),
FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id)
);
2.to create teacher table
CREATE TABLE teacher (
teacher_id INT PRIMARY KEY,
teacher_name VARCHAR(100),
age INT,
subject VARCHAR(50),
experience INT
);
3.insert teachers data in to table
INSERT INTO teacher(teacher_id,teacher_name,age,subject,experience)
VALUES
(101, 'Sk. Sohana', 30, 'Mathematics', 5),
(102, 'U. Munisekhar', 35, 'English', 8),
(103, 'SK. Nellu', 40, 'Science', 10),
(104, 'A. Venu', 28, 'History', 3);
4.insert class data in to table
INSERT INTO class(class_id,class_name,teacher_id)
(9, 'Math', 101),
(10, 'English', 102),
(11, 'Science', 103),
(12, 'History', 104);
teacher Table
| teacher_id | teacher_name | age | subject | experience |
|---|---|---|---|---|
| 101 | Sk. Sohana | 30 | Mathematics | 5 |
| 102 | U. Munisekhar | 35 | English | 8 |
| 103 | SK. Nellu | 40 | Science | 10 |
| 104 | A. Venu | 28 | History | 3 |
| 105 | S. Jagadeesh | 28 | Telugu | 3 |
class Table
| class_id | class_name | teacher_id |
|---|---|---|
| 9 | Math | 101 |
| 10 | English | 102 |
| 11 | Science | 103 |
| 12 | History | 104 |
- To get the data from the Class table
SELECT * FROM class;
| class_id | class_name | teacher_id |
|----------|--------------------|------------|
| 9 | Math | 101 |
| 10 | English | 102 |
| 11 | Science | 103 |
| 12 | History | 104 |
- To get the data from the teacher table 5 year experience teachers
SELECT * FROM teacher WHARE experience >5
| teacher_id | teacher_name | age | subject | experience |
|------------|--------------------|-----|---------------|------------|
| 102 | U. Munisekhar | 35 | English | 8 |
| 103 | SK. Nellu | 40 | Science | 10 |
7.to find Munisekhar teacher deatails
SELECT * FROM teacher WHERE teacher_name='U. Munisekhar'
| teacher_id | teacher_name | age | subject | experience |
|------------|--------------------|-----|---------------|------------|
| 102 | U. Munisekhar | 35 | English | 8 |
8.find Sk. Sohana teacher experience?
SELECT experience FROM teacher WHERE teacher_name='Sk. Sohana';
| experience |
|------------|
| 8 |
9.find the teachers name and age WHERE age bitwen 29 to 39
SELECT name,age FROM teacher WHERE age BETWEEN 29 AND 39;
| teacher_name | age |
|--------------------|-----|
| Sk. Sohana | 30 |
| U. Munisekhar | 35 |
10.to find class name and teacher name to use left join
SELECT class.class_name, teacher.teacher_name
FROM class
RIGHT JOIN teacher ON class.teacher_id=teacher.teacher_id;
| class_name | teacher_name |
|------------|--------------------|
| Math | Sk. Sohana |
| English | U. Munisekhar |
| Science | SK. Nellu |
| History | A. Venu |
11.to find class name and ALL teachers names to use right join
SELECT class.class_name, teacher.teacher_name
FROM class
RIGHT JOIN teacher ON class.teacher_id=teacher.teacher_id;
| class_name | teacher_name |
|------------|--------------------|
| Math | Sk. Sohana |
| English | U. Munisekhar |
| Science | SK. Nellu |
| History | A. Venu |
| NULL | S. Jagadeesh |
12.to find class name and teachers names to use inner join
SELECT class.class_name, teacher.teacher_name
FROM class
INNER JOIN teacher ON class.teacher_id=teacher.teacher_id;
| class_name | teacher_name |
|------------|--------------------|
| Math | Sk. Sohana |
| English | U. Munisekhar |
| Science | SK. Nellu |
| History | A. Venu |
13.to find munisekhar class display heis name and calss
SELECT teacher.teacher.name, class.class_name
FROM teacher
RIGHT JOIN class ON teacher.teacher_id=class.teacher_id
WHERE teacher.teacher_name = 'U. Munisekhar';
| teacher_name | class_name |
|--------------------|------------|
| U. Munisekhar | English |
