Q17.

mysql> CREATE TABLE student ( student_id VARCHAR(10) PRIMARY KEY NOT NULL, name
VARCHAR(30), marks INTEGER(5) );
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> desc student;

+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| student_id | varchar(10) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| marks | int | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
___________________________________________________________________________________
___________________________________________________________________________________
___________________________________________________________________________________
___
Q18.
mysql> INSERT INTO student VALUES(101, 'Rohit', 410); INSERT INTO student
VALUES(102, 'Mohit', 425); INSERT INTO student VALUES(103, 'Rahul', 475); INSERT
INTO student VALUES(104, 'Virat', 495);
Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

mysql> select*from student;

+------------+-------+-------+
| student_id | name | marks |
+------------+-------+-------+
| 101 | Rohit | 410 |
| 102 | Mohit | 425 |
| 103 | Rahul | 475 |
| 104 | Virat | 495 |
+------------+-------+-------+
4 rows in set (0.00 sec)
___________________________________________________________________________________
___________________________________________________________________________________
___________________________________________________________________________________
___
Q19.
mysql> DELETE FROM student WHERE name='Rahul'; SELECT * FROM student;
Query OK, 1 row affected (0.00 sec)

+------------+-------+-------+
| student_id | name | marks |
+------------+-------+-------+
| 101 | Rohit | 410 |
| 102 | Mohit | 425 |
| 104 | Virat | 495 |
+------------+-------+-------+
3 rows in set (0.00 sec)
___________________________________________________________________________________
___________________________________________________________________________________
___________________________________________________________________________________
___
Q20.
mysql> SELECT * FROM student WHERE marks > 80;
+------------+-------+-------+
| student_id | name | marks |
+------------+-------+-------+
| 101 | Rohit | 410 |
| 102 | Mohit | 425 |
| 104 | Virat | 495 |
+------------+-------+-------+
3 rows in set (0.00 sec)
___________________________________________________________________________________
___________________________________________________________________________________
___________________________________________________________________________________
___
Q21.
mysql> CREATE TABLE Customers (
-> customer_ID INT PRIMARY KEY,
-> customer_Name VARCHAR(100)
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO Customers (customer_ID, customer_Name) VALUES

-> (101, 'John Doe'),
-> (102, 'Jane Smith'),
-> (103, 'Michael Brown'),
-> (104, 'Emily Davis'),
-> (105, 'Daniel Wilson');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> CREATE TABLE Orders (

-> order_ID INT PRIMARY KEY,
-> customer_ID INT,
-> order_Date DATE
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO Orders (order_ID, customer_ID, order_Date) VALUES

-> (1, 101, '2024-11-17'),
-> (2, 102, '2024-11-16'),
-> (3, 103, '2024-11-15'),
-> (4, 104, '2024-11-14'),
-> (5, 105, '2024-11-13');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from customers;

+-------------+---------------+
| customer_ID | customer_Name |
+-------------+---------------+
| 101 | John Doe |
| 102 | Jane Smith |
| 103 | Michael Brown |
| 104 | Emily Davis |
| 105 | Daniel Wilson |
+-------------+---------------+
5 rows in set (0.00 sec)
mysql> select * from orders;
+----------+-------------+------------+
| order_ID | customer_ID | order_Date |
+----------+-------------+------------+
| 1 | 101 | 2024-11-17 |
| 2 | 102 | 2024-11-16 |
| 3 | 103 | 2024-11-15 |
| 4 | 104 | 2024-11-14 |
| 5 | 105 | 2024-11-13 |
+----------+-------------+------------+
5 rows in set (0.00 sec)

mysql> CREATE TABLE NewOrders AS

-> SELECT o.order_ID, c.customer_Name, o.order_Date
-> FROM Orders o
-> JOIN Customers c ON o.customer_ID = c.customer_ID;
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> show tables;

+-------------+
| Tables_in_q |
+-------------+
| customers |
| neworders |
| orders |
+-------------+
3 rows in set (0.00 sec)

mysql> select * from neworders;

+----------+---------------+------------+
| order_ID | customer_Name | order_Date |
+----------+---------------+------------+
| 1 | John Doe | 2024-11-17 |
| 2 | Jane Smith | 2024-11-16 |
| 3 | Michael Brown | 2024-11-15 |
| 4 | Emily Davis | 2024-11-14 |
| 5 | Daniel Wilson | 2024-11-13 |
+----------+---------------+------------+
5 rows in set (0.00 sec)
___________________________________________________________________________________
___________________________________________________________________________________
___________________________________________________________________________________
___
Q22.
mysql> ALTER TABLE Orders
-> ADD CONSTRAINT fk_customer
-> FOREIGN KEY (customer_ID) REFERENCES Customers(customer_ID);
Query OK, 5 rows affected (0.04 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> desc orders;;

+-------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------+------+-----+---------+-------+
| order_ID | int | NO | PRI | NULL | |
| customer_ID | int | YES | MUL | NULL | |
| order_Date | date | YES | | NULL | |
+-------------+------+------+-----+---------+-------+
3 rows in set (0.00 sec)
___________________________________________________________________________________
___________________________________________________________________________________
___________________________________________________________________________________
___
Q23.
mysql> select * from student;
+------------+-------+-------+
| student_id | name | marks |
+------------+-------+-------+
| 101 | Rohit | 410 |
| 102 | Mohit | 425 |
| 104 | Virat | 495 |
+------------+-------+-------+
3 rows in set (0.00 sec)

mysql> select min(marks),max(marks),sum(marks),avg(marks) from student;

+------------+------------+------------+------------+
| min(marks) | max(marks) | sum(marks) | avg(marks) |
+------------+------------+------------+------------+
| 410 | 495 | 1330 | 443.3333 |
+------------+------------+------------+------------+
1 row in set (0.00 sec)
___________________________________________________________________________________
___________________________________________________________________________________
___________________________________________________________________________________
___
Q24.

mysql> select * from customers;

+-------------+----------------+-----------+
| customer_ID | customer_Name | country |
+-------------+----------------+-----------+
| 1 | John Doe | USA |
| 2 | Jane Smith | Canada |
| 3 | Michael Brown | USA |
| 4 | Emily Davis | UK |
| 5 | Daniel Wilson | Canada |
| 6 | Sophia Johnson | Australia |
| 7 | Liam Martinez | USA |
| 8 | Olivia Garcia | UK |
+-------------+----------------+-----------+
8 rows in set (0.00 sec)

mysql> SELECT country, COUNT(customer_ID) AS TotalCustomers

-> FROM Customers
-> GROUP BY country;
+-----------+----------------+
| country | TotalCustomers |
+-----------+----------------+
| USA | 3 |
| Canada | 2 |
| UK | 2 |
| Australia | 1 |
+-----------+----------------+
4 rows in set (0.00 sec)
___________________________________________________________________________________
___________________________________________________________________________________
___________________________________________________________________________________
___
Q25.
mysql> select * from studentnames;
+------------+---------------+
| student_ID | name |
+------------+---------------+
| 1 | John Doe |
| 2 | Jane Smith |
| 3 | Michael Brown |
| 4 | Emily Davis |
| 5 | Daniel Wilson |
+------------+---------------+
5 rows in set (0.00 sec)

mysql> select * from studentdob;

+------------+---------------+
| student_ID | date_of_birth |
+------------+---------------+
| 1 | 2000-01-15 |
| 2 | 1999-05-22 |
| 3 | 2001-07-11 |
| 4 | 2000-11-30 |
| 5 | 1998-02-17 |
+------------+---------------+
5 rows in set (0.00 sec)

mysql> CREATE TABLE StudentInfo AS

-> SELECT sn.name, sd.date_of_birth
-> FROM StudentNames sn
-> JOIN StudentDOB sd ON sn.student_ID = sd.student_ID;
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> show tables;

+--------------+
| Tables_in_x |
+--------------+
| customers |
| studentdob |
| studentinfo |
| studentnames |
+--------------+
4 rows in set (0.00 sec)

mysql> select * from studentinfo;

+---------------+---------------+
| name | date_of_birth |
+---------------+---------------+
| John Doe | 2000-01-15 |
| Jane Smith | 1999-05-22 |
| Michael Brown | 2001-07-11 |
| Emily Davis | 2000-11-30 |
| Daniel Wilson | 1998-02-17 |
+---------------+---------------+
5 rows in set (0.00 sec)
___________________________________________________________________________________
___________________________________________________________________________________
__
Q26.
mysql> select * from student;
+------------+-------+-------+
| student_id | name | marks |
+------------+-------+-------+
| 101 | Rohit | 410 |
| 102 | Mohit | 425 |
| 104 | Virat | 495 |
+------------+-------+-------+
3 rows in set (0.00 sec)

mysql> select student_id,marks from student order by marks desc;

+------------+-------+
| student_id | marks |
+------------+-------+
| 104 | 495 |
| 102 | 425 |
| 101 | 410 |
+------------+-------+
3 rows in set (0.00 sec)
___________________________________________________________________________________
___________________________________________________________________________________
___________________________________________________________________________________
___