1.

creating a database/schema
-----------------------------

2 ways
A. command line client

B. Workbench

Query for creating a database

-----------------------------
create database studentdb;

2.make the database active

---------------------------

use databasename;
use studentdb

3.create a table
-----------------
create table studentdetails(name varchar(100),rollno int);

show tables;

describe studentdetails;

constraints-->Not null
Unique
Primary key(unique+not null)
default

1. insert into studentdetails values('abi',101);

2. insert into studentdetails(name) values('abi')-->abi,null

Practise query
--------------

create database salesdetails;

mysql> use salesdetails;

creating primary key at coulmn level at the time of table creation

------------------------------------------------------------------
mysql> create table customers(name varchar(50),cid int primary key
AUTO_INCREMENT,address varchar(100),phonenumber int not null,age numeric default
0);

add constraint after creating table using alter command

-----------------------------------------------------
mysql> alter table customers modify address varchar(300) not null;
not adding primary key while creating table and adding it by using alter command
--------------------------------------------------------------------------------

mysql> create table customers(name varchar(50),cid int,address

varchar(100),phonenumber int not null,age numeric default 0);

mysql> alter table customers add constraint primary key(cid);

creating order details table

----------------------------

mysql> create table orderdetails(orderid int,productname varchar(100),price

float(4,2),custid int , constraint foreign key(custid)references customers(cid)on
delete cascade);
Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> desc customers;

+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| name | varchar(50) | YES | | NULL | |
| cid | int | NO | PRI | NULL | |
| address | varchar(100) | YES | | NULL | |
| phonenumber | int | NO | | NULL | |
| age | decimal(10,0) | YES | | 0 | |
+-------------+---------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> desc orderdetails

-> ;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| orderid | int | YES | | NULL | |
| productname | varchar(100) | YES | | NULL | |
| price | float(4,2) | YES | | NULL | |
| custid | int | YES | MUL | NULL | |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> insert into customers values('Ravi',101,'chennai',123456,25);

Query OK, 1 row affected (0.01 sec)

mysql> insert into customers(name,cid,phonenumber)values('Arun',102,789078);

Query OK, 1 row affected (0.02 sec)

adding same value twice for primary key column will give error
---------------------------------------------------------------

mysql> insert into customers(name,cid,phonenumber)values('Asish',102,789079);

ERROR 1062 (23000): Duplicate entry '102' for key 'customers.PRIMARY'

mysql> insert into customers(name,cid,phonenumber)values('Arun',103,789078);

Query OK, 1 row affected (0.01 sec)

mysql> select * from customers;

+------+-----+---------+-------------+------+
| name | cid | address | phonenumber | age |
+------+-----+---------+-------------+------+
| Ravi | 101 | chennai | 123456 | 25 |
| Arun | 102 | NULL | 789078 | 0 |
| Arun | 103 | NULL | 789078 | 0 |
+------+-----+---------+-------------+------+
3 rows in set (0.00 sec)

mysql> select cid,phonenumber from customers;

+-----+-------------+
| cid | phonenumber |
+-----+-------------+
| 101 | 123456 |
| 102 | 789078 |
| 103 | 789078 |
+-----+-------------+
3 rows in set (0.00 sec)

mysql> alter table customers drop column age;

Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc customers;

+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| name | varchar(50) | YES | | NULL | |
| cid | int | NO | PRI | NULL | |
| address | varchar(100) | YES | | NULL | |
| phonenumber | int | NO | | NULL | |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> select * from customers where cid=101;

+------+-----+---------+-------------+
| name | cid | address | phonenumber |
+------+-----+---------+-------------+
| Ravi | 101 | chennai | 123456 |
+------+-----+---------+-------------+
1 row in set (0.00 sec)

mysql> show tables;

+------------------------+
| Tables_in_salesdetails |
+------------------------+
| customers |
| orderdetails |
+------------------------+
2 rows in set (0.00 sec)

mysql> desc orderdetails;

+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| orderid | int | YES | | NULL | |
| productname | varchar(100) | YES | | NULL | |
| price | float(4,2) | YES | | NULL | |
| custid | int | YES | MUL | NULL | |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> insert into orderdetails values(101,'soap',40.87,102);

Query OK, 1 row affected (0.01 sec)

mysql> insert into orderdetails values(102,'Oppo phone',10000.87,102);

ERROR 1264 (22003): Out of range value for column 'price' at row 1
mysql> insert into orderdetails values(10001,'Oppo phone',10.87,102);
Query OK, 1 row affected (0.01 sec)

mysql> insert into orderdetails values(10002,'laptop',20.32,103);

Query OK, 1 row affected (0.01 sec)

mysql> select * from customers;

+------+-----+---------+-------------+
| name | cid | address | phonenumber |
+------+-----+---------+-------------+
| Ravi | 101 | chennai | 123456 |
| Arun | 102 | NULL | 789078 |
| Arun | 103 | NULL | 789078 |
+------+-----+---------+-------------+
3 rows in set (0.00 sec)

mysql> select * from orderdetails;

+---------+-------------+-------+--------+
| orderid | productname | price | custid |
+---------+-------------+-------+--------+
| 101 | soap | 40.87 | 102 |
| 10001 | Oppo phone | 10.87 | 102 |
| 10002 | laptop | 20.32 | 103 |
+---------+-------------+-------+--------+
3 rows in set (0.00 sec)

adding custid that is not present in parent table will give error
------------------------------------------------------------------

mysql> insert into orderdetails values(10002,'laptop',20.32,105);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails (`salesdetails`.`orderdetails`, CONSTRAINT `orderdetails_ibfk_1` FOREIGN KEY
(`custid`) REFERENCES `customers` (`cid`) ON DELETE CASCADE)