mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| vit |
| world |
+——————–+
7 rows in set (0.02 sec)
mysql> use vit
Database changed
mysql> show tables
-> ;
+———————-+
| Tables_in_vit |
+———————-+
| department_21bce1907 |
| employee_21bce1907 |
+———————-+
2 rows in set (0.01 sec)
mysql> CREATE TABLE member (
-> memb_no NUMBER,
-> name VARCHAR2(100),
-> age NUMBER
-> );
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 ‘NUMBER,
name VARCHAR2(100),
age NUMBER
)’ at line 2
mysql> create table person(
-> driver_id number not null
-> ,
-> name varchar not null,
-> address varchar,
-> PRIMARY KEY(driver_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 ‘number not null
,
name varchar not null,
address varchar,
PRIMARY KEY(driver_id)’ at line 2
mysql> CREATE TABLE member (
-> driver_id number not null,
-> name varchar not null,
-> age int,
-> address varchar,
-> PRIMARY KEY(driver_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 ‘number not null,
name varchar not null,
age int,
address varchar,
PRIMARY KEY(dr’ at line 2
mysql> CREATE TABLE member (
-> driver_id int not null,
-> name varchar not null,
-> address varchar,
-> PRIMARY KEY(driver_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 ‘not null,
address varchar,
PRIMARY KEY(driver_id))’ at line 3
mysql>
mysql> CREATE TABLE member (
-> -> driver_id int not null,
-> -> name varchar not null,
-> -> address varchar,
-> -> address varchar,
->
->
-> ^X^C
mysql> ^C
mysql> Create Table member( driver_id int Primary key, name varchar, address varchar);
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 ‘, address varchar)’ at line 1
mysql> Create Table member( driver_id int Primary key, name varchar(20), address varchar(20));
Query OK, 0 rows affected (0.04 sec)
mysql> create table car(
-> license varchar(20) primary key,model varchar(20), year number));
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 ‘number))’ at line 2
mysql> create table car(
-> license varchar(20) primary key,model varchar(20), year int));
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> create table car(
-> license varchar(20) primary key,model varchar(20), year int);
Query OK, 0 rows affected (0.10 sec)
mysql> create table accident(
-> report_number int primary key, date int, location varchar(40);
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> create table accident(
-> report_number int primary key, date int, location varchar(40));
Query OK, 0 rows affected (0.02 sec)
mysql> create table owns(
-> driver_id int,
-> license varchar(20),
-> foreign key(driver_id) references member(driver_id), foreign key license references car(license));
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 ‘references car(license))’ at line 4
mysql> create table owns(
-> driver_id int,
-> license varchar(20),
-> foreign key(driver_id) references member(driver_id), foreign key (license) references car(license));
Query OK, 0 rows affected (0.03 sec)
mysql> create table participated(
-> report_number int primary key, license varchar(20),driver_id int, damage amount int, foreign key(license) references car(license),foreign key(driver_id) references member(driver_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 ‘amount int, foreign key(license) references car(license),foreign key(driver_id) ‘ at line 2
mysql> create table participated(
-> report_number int primary key, license varchar(20),driver_id int, damage_amount int, foreign key(license) references car(license),foreign key(driver_
id) references member(driver_id));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into member values(1,’Viaan’,’vandalur’);
Query OK, 1 row affected (0.01 sec)
mysql> insert into member values(2,’Ram’,’van’);
Query OK, 1 row affected (0.01 sec)
mysql> insert into member values(3,’shyam’,’vanlo’);
Query OK, 1 row affected (0.01 sec)
mysql> insert into car values(‘DL1098′,’2014g’,’2012′);
Query OK, 1 row affected (0.00 sec)
mysql> insert into car values(‘DL1099′,’2012g’,’2013′);
Query OK, 1 row affected (0.01 sec)
mysql> insert into car values(‘DL1091′,’2015g’,’2014′);
Query OK, 1 row affected (0.01 sec)
mysql> insert into accident values(75,20,’potheri’);
Query OK, 1 row affected (0.01 sec)
mysql> insert into accident values(72,23,’poiy’);
Query OK, 1 row affected (0.01 sec)
mysql> insert into accident values(71,25,’poi’);
Query OK, 1 row affected (0.01 sec)
mysql> insert into owns values(1,’DL1098′);
Query OK, 1 row affected (0.01 sec)
mysql> insert into owns values(2,’DL1099′);
Query OK, 1 row affected (0.01 sec)
mysql> insert into owns values(3,’DL1091′);
Query OK, 1 row affected (0.00 sec)
mysql> insert into participated values(34,’DL1098′,1,76500);
Query OK, 1 row affected (0.01 sec)
mysql> insert into participated values(35,’DL1099′,2,76400);
Query OK, 1 row affected (0.01 sec)
mysql> insert into participated values(36,’DL1091′,2,76300);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT
-> COUNT(*)
-> FROM
-> employees
-> WHERE
-> job_id = 9;
ERROR 1146 (42S02): Table ‘vit.employees’ doesn’t exist
mysql> select count(*) from member where name=’Viaan’;
+———-+
| count(*) |
+———-+
| 1 |
+———-+
1 row in set (0.01 sec)
mysql> select count(*) from accident where report_number=(select report_number from participated where driver_id=(select driver_id where name=’Viaan’));
ERROR 1054 (42S22): Unknown column ‘name’ in ‘where clause’
mysql> select count(*) from accident where report_number=(select report_number from participated where driver_id=(select driver_id where from member name=’Viaan’));
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 member name=’Viaan’))’ at line 1
mysql> select count(*) from accident where report_number=(select report_number from participated where driver_id=(select driver_id where from member where name=’Viaan’));
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 member where name=’Viaan’))’ at line 1
mysql> select count(*) from accident where report_number=(select report_number from participated where driver_id=(select driver_id where from member where name=’Viaan’));
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 member where name=’Viaan’))’ at line 1
mysql> desc participated
-> ;
+—————+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+—————+————-+——+—–+———+——-+
| report_number | int | NO | PRI | NULL | |
| license | varchar(20) | YES | MUL | NULL | |
| driver_id | int | YES | MUL | NULL | |
| damage_amount | int | YES | | NULL | |
+—————+————-+——+—–+———+——-+
4 rows in set (0.01 sec)
mysql> desc accident
-> ;
+—————+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+—————+————-+——+—–+———+——-+
| report_number | int | NO | PRI | NULL | |
| date | int | YES | | NULL | |
| location | varchar(40) | YES | | NULL | |
+—————+————-+——+—–+———+——-+
3 rows in set (0.00 sec)
mysql> select count() from accident where report_number=(select report_number from participated where driver_id=(select driver_id where from member where name=’Viaan’));
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 accident where report_number=(select report_number from participated wher’ at line 1
mysql> select count(*) from accident where report_number=(select report_number from participated where driver_id=(select driver_id where from member where name=’Viaan’));
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 member where name=’Viaan’))’ at line 1
mysql> select count(*) from accident where report_number=(select report_number from participated where driver_id=1);
+———-+
| count(*) |
+———-+
| 0 |
+———-+
1 row in set (0.01 sec)
mysql> select count(*) from accident where report_number=(select report_number from participated where driver_id=(select driver_id from member where name =’Viaan’));
+———-+
| count(*) |
+———-+
| 0 |
+———-+
1 row in set (0.00 sec)
mysql> select count(*) from accident where report_number=(select report_number from participated where driver_id=(select driver_id where from member where name=’Viaan’));
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 member where name=’Viaan’))’ at line 1
mysql> select * accident
-> ;
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 ‘accident’ at line 1
mysql> select * from accident;
+—————+——+———-+
| report_number | date | location |
+—————+——+———-+
| 71 | 25 | poi |
| 72 | 23 | poiy |
| 75 | 20 | potheri |
+—————+——+———-+
3 rows in set (0.00 sec)
mysql> select * from participated
-> ;
+—————+———+———–+—————+
| report_number | license | driver_id | damage_amount |
+—————+———+———–+—————+
| 34 | DL1098 | 1 | 76500 |
| 35 | DL1099 | 2 | 76400 |
| 36 | DL1091 | 2 | 76300 |
+—————+———+———–+—————+
3 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql> select(*) from member where driver_id=1;
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 member where driver_id=1′ at line 1
mysql> select * from member where driver_id=1;
+———–+——-+———-+
| driver_id | name | address |
+———–+——-+———-+
| 1 | Viaan | vandalur |
+———–+——-+———-+
1 row in set (0.00 sec)
mysql> select count(*) from accident where report_number=select report_number from participated where driver_id= select driver_id from member where name=’Vi
aan’;
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 ‘select report_number from participated where driver_id= select driver_id from me’ at line 1
mysql> select count(*) from accident where report_number=(select report_number from participated where driver_id= (select driver_id from member where name=’Viaan’));
+———-+
| count(*) |
+———-+
| 0 |
+———-+
1 row in set (0.00 sec)
mysql> select count(*) from owns where driver_id=select driver
->
-> ;
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 ‘select driver’ at line 1
mysql>
mysql>
mysql> select count(*) from owns where driver_id=(select driver_id from participated where report_number=(select report_number from accident where date=20))
;
+———-+
| count(*) |
+———-+
| 0 |
+———-+
1 row in set (0.00 sec)
mysql> select name,address from member where driver_id=(select driver_id from owns where license=(select license from car where license=’DL1098′));
+——-+———-+
| name | address |
+——-+———-+
| Viaan | vandalur |
+——-+———-+
1 row in set (0.00 sec)
mysql> update participated set damage_amount=12000 where license=’DL1098′;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
