TEXT   14
Untitled
Guest on 16th March 2023 08:39:00 AM


  1. 1.Create a table EMPLOYEE have the following fields (emp_id, name,city,dob,adhar_no)
  2.  
  3. mysql> create table EMPLOYEE(emp_id int,name varchar(25),dob date,adhar_no int);
  4. Query OK, 0 rows affected (0.25 sec
  5.  
  6. mysql> DESC EMPLOYEE;
  7. +----------+-------------+------+-----+---------+-------+
  8. | Field    | Type        | Null | Key | Default | Extra |
  9. +----------+-------------+------+-----+---------+-------+
  10. | emp_id   | int(11)     | YES  |     | NULL    |       |
  11. | name     | varchar(25) | YES  |     | NULL    |       |
  12. | city     | varchar(50) | YES  |     | NULL    |       |
  13. | dob      | date        | YES  |     | NULL    |       |
  14. | adhar_no | int(11)     | YES  |     | NULL    |       |
  15. +----------+-------------+------+-----+---------+-------+
  16. 5 rows in set (0.00 sec)
  17.  
  18. 2.Create a table STUDENT have the fields(roll_no, name, age, address, gender)and add following constraints on it.
  19.     Roll_no – Primary key
  20.     Name – does not allow null values
  21.     Age- must be in between (0-99), does not allow null values
  22.     Gender- default value is ‘m’
  23.    
  24.     mysql> CREATE TABLE STUDENT(roll_no int primary key,name varchar(25) not null,age int not null,address varchar(25),gender varchar(20) default 'm',check (age<100));
  25. Query OK, 0 rows affected (0.22 sec)
  26.  
  27. mysql> desc STUDENT;
  28. +---------+-------------+------+-----+---------+-------+
  29. | Field   | Type        | Null | Key | Default | Extra |
  30. +---------+-------------+------+-----+---------+-------+
  31. | roll_no | int(11)     | NO   | PRI | NULL    |       |
  32. | name    | varchar(25) | NO   |     | NULL    |       |
  33. | age     | int(11)     | NO   |     | NULL    |       |
  34. | address | varchar(25) | YES  |     | NULL    |       |
  35. | gender  | varchar(20) | YES  |     | m       |       |
  36. +---------+-------------+------+-----+---------+-------+
  37. 5 rows in set (0.00 sec)
  38.  
  39.  
  40.  3.Create a temporary table STUDENT1 of STUDENT relation.
  41.  
  42.  mysql> create temporary table STUDENT1 select * from STUDENT;
  43. Query OK, 0 rows affected (0.00 sec)
  44. Records: 0  Duplicates: 0  Warnings: 0
  45.  
  46. mysql> desc STUDENT1;
  47. +---------+-------------+------+-----+---------+-------+
  48. | Field   | Type        | Null | Key | Default | Extra |
  49. +---------+-------------+------+-----+---------+-------+
  50. | roll_no | int(11)     | NO   |     | NULL    |       |
  51. | name    | varchar(25) | NO   |     | NULL    |       |
  52. | age     | int(11)     | NO   |     | NULL    |       |
  53. | address | varchar(25) | YES  |     | NULL    |       |
  54. | gender  | varchar(20) | YES  |     | m       |       |
  55. +---------+-------------+------+-----+---------+-------+
  56. 5 rows in set (0.00 sec)
  57.  
  58.  
  59.  
  60.  4.Create a duplicate table of EMPLOYEE.
  61.  
  62.    mysql> create table if not exists EMPLOYEE_DUP select * from EMPLOYEE;
  63. Query OK, 0 rows affected (0.18 sec)
  64. Records: 0  Duplicates: 0  Warnings: 0
  65.  
  66.  mysql> DESC EMPLOYEE_DUP;
  67. +----------+-------------+------+-----+---------+-------+
  68. | Field    | Type        | Null | Key | Default | Extra |
  69. +----------+-------------+------+-----+---------+-------+
  70. | emp_id   | int(11)     | YES  |     | NULL    |       |
  71. | name     | varchar(25) | YES  |     | NULL    |       |
  72. | city     | varchar(50) | YES  |     | NULL    |       |
  73. | dob      | date        | YES  |     | NULL    |       |
  74. | adhar_no | int(11)     | YES  |     | NULL    |       |
  75. +----------+-------------+------+-----+---------+-------+
  76. 5 rows in set (0.00 sec)
  77.  
  78. 5. Create an INVOICE database and having following tables.
  79.  
  80.    Customer(id, name, address, city, ph_no)
  81.    Product(pid,  pname, price (default 0))                  
  82.    Invoice_master(inv_id, cust_id(FK), inv_date)                  
  83.    Invoice_item(inv_id (FK),  pid(FK), quantity)                
  84.    
  85.  
  86.  
  87.    mysql> create database INVOICE;
  88.    Query OK, 1 row affected (0.00 sec)
  89.    mysql> use INVOICE;
  90. Database changed
  91.  
  92. mysql> create table Customer(id int primary key,name varchar(20),address varchar
  93. (20),city varchar(20),ph_no varchar(20));
  94. Query OK, 0 rows affected (0.19 sec)
  95.  
  96. mysql> desc Customer;
  97. +---------+-------------+------+-----+---------+-------+
  98. | Field   | Type        | Null | Key | Default | Extra |
  99. +---------+-------------+------+-----+---------+-------+
  100. | id      | int(11)     | NO   | PRI | NULL    |       |
  101. | name    | varchar(20) | YES  |     | NULL    |       |
  102. | address | varchar(20) | YES  |     | NULL    |       |
  103. | city    | varchar(20) | YES  |     | NULL    |       |
  104. | ph_no   | varchar(20) | YES  |     | NULL    |       |
  105. +---------+-------------+------+-----+---------+-------+
  106. 5 rows in set (0.01 sec)
  107.  
  108. mysql> create table Product(pid int primary key,pname varchar(20),price int default 0);
  109. Query OK, 0 rows affected (0.19 sec)
  110.  
  111. mysql> desc Product;
  112. +-------+-------------+------+-----+---------+-------+
  113. | Field | Type        | Null | Key | Default | Extra |
  114. +-------+-------------+------+-----+---------+-------+
  115. | pid   | int(11)     | NO   | PRI | NULL    |       |
  116. | pname | varchar(20) | YES  |     | NULL    |       |
  117. | price | int(11)     | YES  |     | 0       |       |
  118. +-------+-------------+------+-----+---------+-------+
  119. 3 rows in set (0.00 sec)
  120.  
  121. mysql> create table invoice_master(inv_id int primary key,id int,inv_date date,foreign key(id) references Customer(id));
  122. Query OK, 0 rows affected (0.20 sec)
  123.  
  124. mysql> desc invoice_master;
  125. +----------+---------+------+-----+---------+-------+
  126. | Field    | Type    | Null | Key | Default | Extra |
  127. +----------+---------+------+-----+---------+-------+
  128. | inv_id   | int(11) | NO   | PRI | NULL    |       |
  129. | id       | int(11) | YES  | MUL | NULL    |       |
  130. | inv_date | date    | YES  |     | NULL    |       |
  131. +----------+---------+------+-----+---------+-------+
  132. 3 rows in set (0.00 sec)
  133.  
  134. mysql> create table invoice_item(inv_id int,pid int,quantity int,foreign key(inv_id) references invoice_master(inv_id),foreign key(pid) references Product(pid));
  135. Query OK, 0 rows affected (0.24 sec)
  136.  
  137. mysql> desc invoice_item;
  138. +----------+---------+------+-----+---------+-------+
  139. | Field    | Type    | Null | Key | Default | Extra |
  140. +----------+---------+------+-----+---------+-------+
  141. | inv_id   | int(11) | YES  | MUL | NULL    |       |
  142. | pid      | int(11) | YES  | MUL | NULL    |       |
  143. | quantity | int(11) | YES  |     | NULL    |       |
  144. +----------+---------+------+-----+---------+-------+
  145. 3 rows in set (0
Raw Paste

Login or Register to edit or fork this paste. It's free.