Scribd
Upload
80 views14 pages

DBMS Ass

The document details a series of MySQL commands executed in a database environment, including creating a new database and tables for books, customers, and transactions. It also shows the insertion of records into these tables and various SQL operations such as selecting, deleting, and displaying data. Additionally, it highlights error handling for syntax issues and foreign key constraints during data manipulation.

Uploaded by

arshinde4336v
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
80 views14 pages

DBMS Ass

The document details a series of MySQL commands executed in a database environment, including creating a new database and tables for books, customers, and transactions. It also shows the insertion of records into these tables and various SQL operations such as selecting, deleting, and displaying data. Additionally, it highlights error handling for syntax issues and foreign key constraints during data manipulation.

Uploaded by

arshinde4336v
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 14

C:\Program Files\MySQL\MySQL Server 8.

0\bin>mysql -u root -p

Enter password: **********

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 8.0.40 MySQL Community Server - GPL

Copyright (c) 2000, 2024, 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> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| collage |

| hotel_management |

| information_schema |

| mysql |

| performance_schema |

| sakila |

| sys |

| world |

+--------------------+

8 rows in set (0.14 sec)

mysql> CREATE DATABASE ASHISH_CHAVAN;

Query OK, 1 row affected (0.02 sec)


mysql> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| ashish_chavan |

| collage |

| hotel_management |

| information_schema |

| mysql |

| performance_schema |

| sakila |

| sys |

| world |

+--------------------+

9 rows in set (0.00 sec)

mysql> USE ASHISH_CHAVAN;

Database changed

mysql> CREATE TABLE BOOKS(

-> BOOK_ID INT PRIMARY KEY,

-> TITLE VARCHAR(100),

-> AUTHOR VARCHAR(100),

-> GENRE VARCHAR(200),

-> PRICE DOUBLE(10,2),

-> STOCK INT NOT NULL

-> );

Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> DESC BOOKS;

+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |

+---------+--------------+------+-----+---------+-------+

| BOOK_ID | int | NO | PRI | NULL | |

| TITLE | varchar(100) | YES | | NULL | |

| AUTHOR | varchar(100) | YES | | NULL | |

| GENRE | varchar(200) | YES | | NULL | |

| PRICE | double(10,2) | YES | | NULL | |

| STOCK | int | NO | | NULL | |

+---------+--------------+------+-----+---------+-------+

6 rows in set (0.01 sec)

mysql> CREATE TABLE CUSTOMER(

-> CUSTOMER_ID INT PRIMARY KEY,

-> CUST_NAME VARCHAR(200),

-> CUST_EMAIL VARCHAR(200),

-> CUST_PH_NO VARCHAR(15) NOT NULL,

-> CUST_ADDRESS VARCHAR(300)

-> );

Query OK, 0 rows affected (0.03 sec)

mysql> DESC CUSTOMER;

+--------------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------------+--------------+------+-----+---------+-------+

| CUSTOMER_ID | int | NO | PRI | NULL | |

| CUST_NAME | varchar(200) | YES | | NULL | |

| CUST_EMAIL | varchar(200) | YES | | NULL | |

| CUST_PH_NO | varchar(15) | NO | | NULL | |

| CUST_ADDRESS | varchar(300) | YES | | NULL | |

+--------------+--------------+------+-----+---------+-------+

5 rows in set (0.00 sec)


mysql> CREATE TABLE TRANSACTION(

-> TRANSACTION_ID INT PRIMARY KEY,

-> CUSTOMER_ID INT PRIMARY KEY,

-> BOOK_ID INT PRIMARY KEY,

-> PURCHASE_DATE DATE,

-> AMOUNT DOUBLE(10,2)

-> ,

-> FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID),

-> FOREIGN KEY (BOOK_ID) REFERENCES BOOKS(BOOK_ID)

-> );

ERROR 1068 (42000): Multiple primary key defined

mysql> CREATE TABLE TRANSACTION(

-> TRANSACTION_ID INT PRIMARY KEY,

-> CUSTOMER_ID INT,

-> BOOK_ID INT ,

-> PURCHASE_DATE DATE,

-> AMOUNT DOUBLE(10,2),

-> FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID),

-> FOREIGN KEY (BOOK_ID) REFERENCES BOOKS(BOOK_ID)

-> );

Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> DESC TRANSACTION;

+----------------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------------+--------------+------+-----+---------+-------+

| TRANSACTION_ID | int | NO | PRI | NULL | |

| CUSTOMER_ID | int | YES | MUL | NULL | |

| BOOK_ID | int | YES | MUL | NULL | |

| PURCHASE_DATE | date | YES | | NULL | |


| AMOUNT | double(10,2) | YES | | NULL | |

+----------------+--------------+------+-----+---------+-------+

5 rows in set (0.00 sec)

mysql> INSERT INTO BOOKS(BOOK_ID,TITLE,AUTHOR,GENRE,PRICE,STOCK)

-> VALAUE(001,'ATOMIC HABIT','JAMES CLEAR','NON FICTION',250.00,50),

-> VALAUE(002,'HAMLET','WILLIAM SHAKESPEARE','FICTION',200.00,30)

-> );

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 'VALAUE(001,'ATOMIC HABIT','JAMES
CLEAR','NON FICTION',250.00,50),

VALAUE(002,'HA' at line 2

mysql> INSERT INTO BOOKS(BOOK_ID,TITLE,AUTHOR,GENRE,PRICE,STOCK)

-> VALUES

-> VALAUE(001,'ATOMIC HABIT','JAMES CLEAR','NON FICTION',250.00,50),

-> VALAUE(002,'HAMLET','WILLIAM SHAKESPEARE','FICTION',200.00,30)

-> );

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 'VALAUE(001,'ATOMIC HABIT','JAMES
CLEAR','NON FICTION',250.00,50),

VALAUE(002,'HA' at line 3

mysql> INSERT INTO BOOKS(BOOK_ID,TITLE,AUTHOR,GENRE,PRICE,STOCK)

-> VALUES

-> (001,'ATOMIC HABIT','JAMES CLEAR','NON FICTION',250.00,50),

-> (002,'HAMLET','WILLIAM SHAKESPEARE','FICTION',200.00,30)

-> );

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 ')' at line 5

mysql> INSERT INTO BOOKS(BOOK_ID, TITLE, AUTHOR, GENRE, PRICE, STOCK)

-> VALUES

-> (001, 'ATOMIC HABIT', 'JAMES CLEAR', 'NON FICTION', 250.00, 50),

-> (002, 'HAMLET', 'WILLIAM SHAKESPEARE', 'FICTION', 200.00, 30);

Query OK, 2 rows affected (0.02 sec)


Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT* FROM BOOKS;

+---------+--------------+---------------------+-------------+--------+-------+

| BOOK_ID | TITLE | AUTHOR | GENRE | PRICE | STOCK |

+---------+--------------+---------------------+-------------+--------+-------+

| 1 | ATOMIC HABIT | JAMES CLEAR | NON FICTION | 250.00 | 50 |

| 2 | HAMLET | WILLIAM SHAKESPEARE | FICTION | 200.00 | 30 |

+---------+--------------+---------------------+-------------+--------+-------+

2 rows in set (0.00 sec)

mysql> INSERT INTO BOOKS(BOOK_ID, TITLE, AUTHOR, GENRE, PRICE, STOCK)

-> VALUES

-> (003, ' Discovery of India', 'Jawaharlal Nehru', 'HISTORY', 250.00, 25),

-> (004, ' SCIENCE AND TECHNOLOGY', 'RAVI.P.AGRAHAR', 'SCIENCE AND TECHNOLOGY', 400.00,
25),

-> (005, ' ENVIROMENTAL SUSTAINABILITY', 'KANE HARLOW', 'ENVIROMENT', 500.00, 25);

Query OK, 3 rows affected (0.02 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM BOOKS;

+---------+------------------------------+---------------------+------------------------+--------+-------+

| BOOK_ID | TITLE | AUTHOR | GENRE | PRICE | STOCK |

+---------+------------------------------+---------------------+------------------------+--------+-------+

| 1 | ATOMIC HABIT | JAMES CLEAR | NON FICTION | 250.00 | 50 |

| 2 | HAMLET | WILLIAM SHAKESPEARE | FICTION | 200.00 | 30 |

| 3 | Discovery of India | Jawaharlal Nehru | HISTORY | 250.00 | 25 |

| 4 | SCIENCE AND TECHNOLOGY | RAVI.P.AGRAHAR | SCIENCE AND TECHNOLOGY | 400.00


| 25 |

| 5 | ENVIROMENTAL SUSTAINABILITY | KANE HARLOW | ENVIROMENT | 500.00 |


25 |

+---------+------------------------------+---------------------+------------------------+--------+-------+
5 rows in set (0.00 sec)

INSERT INTO CUSTOMER (CUSTOMER_ID, CUST_NAME, CUST_EMAIL, CUST_PH_NO, CUST_ADDRESS)


VALUES

-> (1, 'Ashish Chavan', '[email protected]', '7756845546', 'Pune'),

-> (2, 'Somya Juwatkar', '[email protected]', '3456712899', 'Pimpri'),

-> (3, 'Amit Rai', '[email protected]', '5269873411', 'Mumbai'),

-> (4, 'Anshika Pawar', '[email protected]', '7898125612', 'Dhule');

Query OK, 4 rows affected (0.01 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from customer;

+-------------+----------------+------------------------+------------+--------------+

| CUSTOMER_ID | CUST_NAME | CUST_EMAIL | CUST_PH_NO | CUST_ADDRESS |

+-------------+----------------+------------------------+------------+--------------+

| 1 | Ashish Chavan | [email protected] | 7756845546 | Pune |

| 2 | Somya Juwatkar | [email protected] | 3456712899 | Pimpri |

| 3 | Amit Rai | [email protected] | 5269873411 | Mumbai |

| 4 | Anshika Pawar | [email protected] | 7898125612 | Dhule |

+-------------+----------------+------------------------+------------+--------------+

4 rows in set (0.00 sec)

mysql> mysql> INSERT INTO TRANSACTION (TRANSACTION_ID, CUSTOMER_ID, BOOK_ID,


PURCHASE_DATE, AMOUNT) VALUES

-> (1, 1, 001, '2025-02-20', 299.99),

-> (2, 2, 003, '2025-02-21', 499.99),

-> (3, 1, 003, '2025-02-22', 699.99),

-> (4, 3, 004, '2025-02-23', 399.50),

-> (5, 4, 005, '2025-02-24', 599.00);

Query OK, 5 rows affected (0.01 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from transaction;


+----------------+-------------+---------+---------------+--------+

| TRANSACTION_ID | CUSTOMER_ID | BOOK_ID | PURCHASE_DATE | AMOUNT |

+----------------+-------------+---------+---------------+--------+

| 1| 1| 1 | 2025-02-20 | 299.99 |

| 2| 2| 3 | 2025-02-21 | 499.99 |

| 3| 1| 3 | 2025-02-22 | 699.99 |

| 4| 3| 4 | 2025-02-23 | 399.50 |

| 5| 4| 5 | 2025-02-24 | 599.00 |

+----------------+-------------+---------+---------------+--------+

5 rows in set (0.00 sec)

mysql> DELETE FROM BOOKS

-> WHERE BOOK_ID IN (3, 4, 5);

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
(`ashish_chavan`.`transaction`, CONSTRAINT `transaction_ibfk_2` FOREIGN KEY (`BOOK_ID`)
REFERENCES `books` (`BOOK_ID`))

mysql> DELETE FROM TRANSACTION WHERE BOOK_ID IN (3, 4, 5);

Query OK, 4 rows affected (0.01 sec)

mysql> DELETE FROM BOOKS WHERE BOOK_ID IN (3, 4, 5);

Query OK, 3 rows affected (0.00 sec)

mysql> DELETE FROM BOOKS

-> WHERE BOOK_ID IN (3, 4, 5);

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO BOOKS (BOOK_ID, TITLE, AUTHOR, GENRE, PRICE, STOCK)

-> VALUES

-> (3, 'Discovery of India', 'Jawaharlal Nehru', 'HISTORY', 250.00, 25),

-> (4, 'Science and Technology', 'Ravi P. Agrahar', 'SCIENCE AND TECHNOLOGY', 400.00, 25),

-> (5, 'Environmental Sustainability', 'Kane Harlow', 'ENVIRONMENT', 500.00, 0);

Query OK, 3 rows affected (0.00 sec)


Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from books;

+---------+------------------------------+---------------------+------------------------+--------+-------+

| BOOK_ID | TITLE | AUTHOR | GENRE | PRICE | STOCK |

+---------+------------------------------+---------------------+------------------------+--------+-------+

| 1 | ATOMIC HABIT | JAMES CLEAR | NON FICTION | 250.00 | 50 |

| 2 | HAMLET | WILLIAM SHAKESPEARE | FICTION | 200.00 | 30 |

| 3 | Discovery of India | Jawaharlal Nehru | HISTORY | 250.00 | 25 |

| 4 | Science and Technology | Ravi P. Agrahar | SCIENCE AND TECHNOLOGY | 400.00 | 25


|

| 5 | Environmental Sustainability | Kane Harlow | ENVIRONMENT | 500.00 | 0|

+---------+------------------------------+---------------------+------------------------+--------+-------+

5 rows in set (0.00 sec)

mysql> DELETE FROM BOOKS

-> WHERE STOCK=0;

Query OK, 1 row affected (0.01 sec)

mysql> select * from books;

+---------+------------------------+---------------------+------------------------+--------+-------+

| BOOK_ID | TITLE | AUTHOR | GENRE

| PRICE | STOCK |

+---------+------------------------+---------------------+------------------------+--------+-------+

| 1 | ATOMIC HABIT | JAMES CLEAR | NON FICTION

| 250.00 | 50 |

| 2 | HAMLET | WILLIAM SHAKESPEARE | FICTION

| 200.00 | 30 |

| 3 | Discovery of India | Jawaharlal Nehru | HISTORY

| 250.00 | 25 |

| 4 | Science and Technology | Ravi P. Agrahar | SCIENCE AND TECHNOLOGY | 400.00 | 25 |


+---------+------------------------+---------------------+------------------------+--------+-------+

4 rows in set (0.00 sec)

mysql> select * from books;

+---------+------------------------+---------------------+------------------------+--------+-------+

| BOOK_ID | TITLE | AUTHOR | GENRE | PRICE | STOCK |

+---------+------------------------+---------------------+------------------------+--------+-------+

| ID | TIOMIC HABIT | JAMES CLEAR | NON FICTION | 250.00 | 50 |

| 2 | HAMLET | WILLIAM SHAKESPEARE | FICTION | 200.00 | 30 |

| 3 | Discovery of India | Jawaharlal Nehru | HISTORY | 250.00 | 25 |

| 4 | Science and Technology | Ravi P. Agrahar | SCIENCE AND TECHNOLOGY | 400.00 | 25 |

+---------+------------------------+---------------------+------------------------+--------+-------+

4 rows in set (0.00 sec)

mysql> update

mysql>

mysql> ;

ERROR:

No query specified

mysql> UPDATE CUSTOMER

-> SET ADDRESS='DEHU ROAD'

-> WHERE CUSTOMER_ID=2;

ERROR 1054 (42S22): Unknown column 'ADDRESS' in 'field list'

mysql> UPDATE CUSTOMER

-> SET CUST_ADDRESS='DEHU ROAD'

-> WHERE CUSTOMER_ID=2;


Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT* FROM CUSTOMER;

+-------------+----------------+------------------------+------------+--------------+

| CUSTOMER_ID | CUST_NAME | CUST_EMAIL | CUST_PH_NO | CUST_ADDRESS |

+-------------+----------------+------------------------+------------+--------------+

| 1 | Ashish Chavan | [email protected] | 7756845546 | Pune |

| 2 | Somya Juwatkar | [email protected] | 3456712899 | DEHU ROAD |

| 3 | Amit Rai | [email protected] | 5269873411 | Mumbai |

| 4 | Anshika Pawar | [email protected] | 7898125612 | Dhule |

+-------------+----------------+------------------------+------------+--------------+

4 rows in set (0.00 sec)

mysql> SELECT TITLE

-> FROM BOOKS

-> ORDERED BY DESCENDING;

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 'BY DESCENDING' at line 3

mysql> SELECT TITLE

-> FROM BOOKS

-> ORDERD BY PRICE 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 'BY PRICE DESC' at line 3

mysql> SELECT * FROM BOOKS

-> ORDER BY PRICE DESC;

+---------+------------------------+---------------------+------------------------+--------+-------+

| BOOK_ID | TITLE | AUTHOR | GENRE | PRICE | STOCK |

+---------+------------------------+---------------------+------------------------+--------+-------+

| 4 | Science and Technology | Ravi P. Agrahar | SCIENCE AND TECHNOLOGY | 400.00 | 25 |

| 1 | ATOMIC HABIT | JAMES CLEAR | NON FICTION | 250.00 | 50 |

| 3 | Discovery of India | Jawaharlal Nehru | HISTORY | 250.00 | 25 |


| 2 | HAMLET | WILLIAM SHAKESPEARE | FICTION | 200.00 | 30 |

+---------+------------------------+---------------------+------------------------+--------+-------+

4 rows in set (0.00 sec)

mysql> SELECT TITLE

-> FROM BOOKS

-> ORDER BY PRICE DESC;

+------------------------+

| TITLE |

+------------------------+

| Science and Technology |

| ATOMIC HABIT |

| Discovery of India |

| HAMLET |

+------------------------+

4 rows in set (0.00 sec)

mysql> SELECT CUSTOMER_ID, SUM(AMOUNT) AS Total_Spent

-> FROM TRANSACTION

-> GROUP BY CUSTOMER_ID;

+-------------+-------------+

| CUSTOMER_ID | Total_Spent |

+-------------+-------------+

| 1| 299.99 |

+-------------+-------------+

1 row in set (0.01 sec)

mysql> SELECT TITLE

-> FROM BOOKS

-> WHERE TITLE LIKE '%HISTORY%';

Empty set (0.00 sec)


mysql> ALTER TABLE CUSTOMER

-> MODIFY COLOUMN CUST_PH_NO

-> VARCHAR(15) NOT NULL;

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 'CUST_PH_NO

VARCHAR(15) NOT NULL' at line 2

mysql> ALTER TABLE CUSTOMER

-> MODIFY COLUMN CUST_PH_NO VARCHAR(20) NOT NULL;

Query OK, 0 rows affected (0.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> DESC CUSTOMER;

+--------------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------------+--------------+------+-----+---------+-------+

| CUSTOMER_ID | int | NO | PRI | NULL | |

| CUST_NAME | varchar(200) | YES | | NULL | |

| CUST_EMAIL | varchar(200) | YES | | NULL | |

| CUST_PH_NO | varchar(20) | NO | | NULL | |

| CUST_ADDRESS | varchar(300) | YES | | NULL | |

+--------------+--------------+------+-----+---------+-------+

5 rows in set (0.01 sec)

mysql> INSERT INTO CUSTOMER

-> (;

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 '' at line 2

mysql> INSERT INTO CUSTOMER (CUSTOMER_ID, CUST_NAME, CUST_EMAIL, CUST_PH_NO,


CUST_ADDRESS) VALUES

-> -> (1, 'Ashish Chavan', '[email protected]', '7756845546', 'Pune'),

-> L;
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 '-> (1, 'Ashish Chavan',
'[email protected]', '7756845546', 'Pune'),

L' at line 2

mysql> INSERT INTO CUSTOMER (CUSTOMER_ID, CUST_NAME, CUST_EMAIL, CUST_PH_NO,


CUST_ADDRESS) VALUES

-> -> (1, 'Ashish Chavan', '[email protected]', '7756845546', 'Pune'),

-> -> (1, 'Ashish Chavan', '[email protected]', '7756845546', 'Pune'),;

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 '-> (1, 'Ashish Chavan',
'[email protected]', '7756845546', 'Pune'),

-> ' at line 2

mysql> INSERT INTO CUSTOMER (CUSTOMER_ID, CUST_NAME, CUST_EMAIL, CUST_PH_NO,


CUST_ADDRESS) VALUES

-> (5, 'Akshara Gade', '[email protected]', null, 'Pune');

ERROR 1048 (23000): Column 'CUST_PH_NO' cannot be null

mysql>

You might also like