mysql> select * from students;
+----+------------+
| id | first_name |
+----+------------+
| 1 | ramesh |
| 2 | vinod |
| 3 | lakshman |
| 4 | praveen |
| 5 | harika |
+----+------------+
5 rows in set (0.00 sec)
mysql> select * from papers;
+--------+-------+------------+
| title | grade | student_id |
+--------+-------+------------+
| ds | 61 | 1 |
| c | 72 | 1 |
| java | 95 | 2 |
| python | 99 | 2 |
| telugu | 85 | 4 |
+--------+-------+------------+
5 rows in set (0.00 sec)
mysql> desc students;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| first_name | varchar(100) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
2 rows in set (0.09 sec)
mysql> desc papers;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| title | varchar(100) | YES | | NULL | |
| grade | int | YES | | NULL | |
| student_id | int | YES | MUL | NULL | |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
--------------------------------------
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(100)
);
CREATE TABLE papers (
title VARCHAR(100),
grade INT,
student_id INT,
FOREIGN KEY (student_id)
REFERENCES students(id)
ON DELETE CASCADE
);
-------------
INSERT INTO students (first_name) VALUES
('ramesh'),
('vinod'),
('lakshman'),
('praveen'),
('harika');
INSERT INTO papers (student_id, title, grade ) VALUES
(1, 'ds', 61),
(1, 'c', 72),
(2, 'java', 95),
(2, 'python', 99),
(4, 'telugu', 85);
------------------
+------------+--------+-------+
| first_name | title | grade |
+------------+--------+-------+
| vinod | python | 99 |
| vinod | java | 95 |
| praveen | telugu | 85 |
| ramesh | c | 72 |
| ramesh | ds | 61 |
+------------+--------+-------+
5 rows in set (0.00 sec)
SELECT first_name, title, grade
FROM students
INNER JOIN papers
ON students.id = papers.student_id
ORDER BY grade DESC;
-------------------------------------
SELECT
first_name,
IFNULL(title, 'MISSING') as title,
IFNULL(grade, 0) as grade
from students left join papers
ON students.id = papers.student_id;
SELECT first_name, title, grade
FROM students
LEFT JOIN papers
ON students.id = papers.student_id;
-----------------------------------
SELECT
first_name,
IFNULL(title, 'MISSING'),
IFNULL(grade, 0)
FROM students
LEFT JOIN papers
ON students.id = papers.student_id;
------------------------
-------------------------
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.25 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use ram
Database changed
mysql> show tables;
+---------------+
| Tables_in_ram |
+---------------+
| customers |
| emp |
| orders |
| papers |
| pay |
| students |
+---------------+
6 rows in set (0.03 sec)
mysql> select * from students;
+----+------------+
| id | first_name |
+----+------------+
| 1 | ramesh |
| 2 | vinod |
| 3 | lakshman |
| 4 | praveen |
| 5 | harika |
+----+------------+
5 rows in set (0.00 sec)
mysql> select * from papers;
+--------+-------+------------+
| title | grade | student_id |
+--------+-------+------------+
| ds | 61 | 1 |
| c | 72 | 1 |
| java | 95 | 2 |
| python | 99 | 2 |
| telugu | 85 | 4 |
+--------+-------+------------+
5 rows in set (0.00 sec)
mysql> desc students;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| first_name | varchar(100) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
2 rows in set (0.09 sec)
mysql> desc papers;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| title | varchar(100) | YES | | NULL | |
| grade | int | YES | | NULL | |
| student_id | int | YES | MUL | NULL | |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> ;C:\Users\VLR Training\Desktop\sql recording - Shortcut.lnk"
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"C:\Users\VLR Training\Desktop\sql recording - Shortcut.lnk"' at line 1
mysql> drop table students,papers;
Query OK, 0 rows affected (2.19 sec)
mysql> CREATE TABLE students (
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> first_name VARCHAR(100)
-> );
Query OK, 0 rows affected (1.24 sec)
mysql> CREATE TABLE papers (
-> title VARCHAR(100),
-> grade INT,
-> student_id INT,
-> FOREIGN KEY (student_id)
-> REFERENCES students(id)
-> ON DELETE CASCADE
-> );
Query OK, 0 rows affected (1.37 sec)
mysql> INSERT INTO students (first_name) VALUES
-> ('ramesh'),
-> ('vinod'),
-> ('lakshman'),
-> ('praveen'),
-> ('harika');
Query OK, 5 rows affected (0.16 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> INSERT INTO papers (student_id, title, grade ) VALUES
-> (1, 'ds', 61),
-> (1, 'c', 72),
-> (2, 'java', 95),
-> (2, 'python', 99),
-> (4, 'telugu', 85);
Query OK, 5 rows affected (0.08 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT first_name, title, grade
-> FROM students
-> INNER JOIN papers
-> ON students.id = papers.student_id
-> ORDER BY grade DESC;
+------------+--------+-------+
| first_name | title | grade |
+------------+--------+-------+
| vinod | python | 99 |
| vinod | java | 95 |
| praveen | telugu | 85 |
| ramesh | c | 72 |
| ramesh | ds | 61 |
+------------+--------+-------+
5 rows in set (0.00 sec)
mysql> select first_name,title,grade from students inner join papers
-> on students.id=papers.student_id orderby grade desc;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'orderby grade desc' at line 2
mysql> select first_name,title,grade from students inner join papers
-> on students.id=papers.student_id order by grade desc;
+------------+--------+-------+
| first_name | title | grade |
+------------+--------+-------+
| vinod | python | 99 |
| vinod | java | 95 |
| praveen | telugu | 85 |
| ramesh | c | 72 |
| ramesh | ds | 61 |
+------------+--------+-------+
5 rows in set (0.00 sec)
mysql> SELECT first_name, title, grade
-> FROM students
-> LEFT JOIN papers
-> ON students.id = papers.student_id;
+------------+--------+-------+
| first_name | title | grade |
+------------+--------+-------+
| ramesh | ds | 61 |
| ramesh | c | 72 |
| vinod | java | 95 |
| vinod | python | 99 |
| lakshman | NULL | NULL |
| praveen | telugu | 85 |
| harika | NULL | NULL |
+------------+--------+-------+
7 rows in set (0.00 sec)
mysql> SELECT first_name, title, grade
-> FROM students
-> LEFT JOIN papers
-> ON students.id = papers.student_id;
+------------+--------+-------+
| first_name | title | grade |
+------------+--------+-------+
| ramesh | ds | 61 |
| ramesh | c | 72 |
| vinod | java | 95 |
| vinod | python | 99 |
| lakshman | NULL | NULL |
| praveen | telugu | 85 |
| harika | NULL | NULL |
+------------+--------+-------+
7 rows in set (0.00 sec)
mysql> SELECT
-> first_name,
-> IFNULL(title, 'MISSING'),
-> IFNULL(grade, 0)
-> FROM students
-> LEFT JOIN papers
-> ON students.id = papers.student_id;
+------------+--------------------------+------------------+
| first_name | IFNULL(title, 'MISSING') | IFNULL(grade, 0) |
+------------+--------------------------+------------------+
| ramesh | ds | 61 |
| ramesh | c | 72 |
| vinod | java | 95 |
| vinod | python | 99 |
| lakshman | MISSING | 0 |
| praveen | telugu | 85 |
| harika | MISSING | 0 |
+------------+--------------------------+------------------+
7 rows in set (0.00 sec)
mysql> SELECT
-> first_name,
-> IFNULL(title, 'MISSING') as title,
-> IFNULL(grade, 0) as grade,
-> from students left join papers on
-> students.id=papers.student_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from students left join papers on
students.id=papers.student_id' at line 5
mysql> SELECT
-> first_name,
-> IFNULL(title, 'MISSING') as title,
-> IFNULL(grade, 0) as grade,
-> from students left join papers on
-> students.id = papers.student_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from students left join papers on
students.id = papers.student_id' at line 5
mysql> SELECTSELECT
-> first_name,
-> IFNULL(title, 'MISSING'),
-> IFNULL(grade, 0)
-> FROM students
-> LEFT JOIN papers
-> ON students.id = papers.student_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECTSELECT
first_name,
IFNULL(title, 'MISSING'),
IFNULL(grade, 0)
' at line 1
mysql> SELECT
-> first_name,
-> IFNULL(title, 'MISSING') as title,
-> IFNULL(grade, 0) as grade,
-> from students left join papers
-> on students.id = papers.student_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from students left join papers
on students.id = papers.student_id' at line 5
mysql> SELECT
-> first_name,
-> IFNULL(title, 'MISSING') as title,
-> IFNULL(grade, 0) as grade,
-> from students left join papers
-> ON students.id = papers.student_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from students left join papers
ON students.id = papers.student_id' at line 5
mysql> SELECT
-> first_name,
-> IFNULL(title, 'MISSING') as title,
-> IFNULL(grade, 0) as grade
-> from students left join papers
-> ON students.id = papers.student_id;
+------------+---------+-------+
| first_name | title | grade |
+------------+---------+-------+
| ramesh | ds | 61 |
| ramesh | c | 72 |
| vinod | java | 95 |
| vinod | python | 99 |
| lakshman | MISSING | 0 |
| praveen | telugu | 85 |
| harika | MISSING | 0 |
+------------+---------+-------+
7 rows in set (0.00 sec)
mysql> SELECT
-> first_name,
-> IFNULL(title, 'MISSING') as title,
-> IFNULL(grade, 0) as grade
-> from students left join papers
-> ON students.id = papers.student_id;
+------------+---------+-------+
| first_name | title | grade |
+------------+---------+-------+
| ramesh | ds | 61 |
| ramesh | c | 72 |
| vinod | java | 95 |
| vinod | python | 99 |
| lakshman | MISSING | 0 |
| praveen | telugu | 85 |
| harika | MISSING | 0 |
+------------+---------+-------+
7 rows in set (0.00 sec)
mysql>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.