SQL || MySQL ||作者:穆尼塞卡·烏達瓦拉帕蒂

Mary-Kate Olsen
發布: 2024-10-13 06:15:30
原創
894 人瀏覽過

SQL || MySQL || By Munisekhar Udavalapati

1.SQL part2

1.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
  1. 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        |

登入後複製
  1. 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    |
登入後複製

以上是SQL || MySQL ||作者:穆尼塞卡·烏達瓦拉帕蒂的詳細內容。更多資訊請關注PHP中文網其他相關文章!

來源:dev.to
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
作者最新文章
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板