KEMBAR78
Query Practice | PDF | Computing | Computer Data
Scribd
Upload
34 views12 pages

Query Practice

The document details the creation of an Employee table and Incentive table in a MySQL database called practice. It inserts sample data into both tables and runs select queries to view the inserted data.

Uploaded by

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

Query Practice

The document details the creation of an Employee table and Incentive table in a MySQL database called practice. It inserts sample data into both tables and runs select queries to view the inserted data.

Uploaded by

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

Create Table Employee

(
emp_id int,
fname varchar(10),
lname varchar(10),
salary int,
joining_date date,
department varchar(10)

);

Insert into Employee values(1,'Jhon','Abel',10000,'2013-01-01','Banking');


Insert into Employee values(2,'Michale','Cler',20000,'2013-01-01','Insurance');
Insert into Employee values(3,'Roy','Thomas',90000,'2013-02-01','Banking');
Insert into Employee values(4,'Tom','Joss',80000,'2013-01-01','Insurance');
Insert into Employee values(5,'Jerry','Pinto',68000,'2013-01-01','Services');

create table incentive


(
employee_ref_id int,
incentive date,
incentive_amount int

);

insert into incentive values(1,'2013-02-01',5000);


insert into incentive values(2,'2013-02-01',3000);
insert into incentive values(3,'2013-02-01',4000);
insert into incentive values(1,'2013-02-01',4500);
insert into incentive values(2,'2013-01-01',3500);

mysql> show databases;


+----------------------+
| Database |
+----------------------+
| blogdb |
| creditcarddb |
| dac |
| dac1 |
| dairy |
| dairyworld |
| farmersystem |
| healthcare |
| hibernatemapping |
| information_schema |
| mysql |
| onlinefurniturestore |
| performance_schema |
| shoppingdb |
| studentsdb |
| supriyaorder |
| sys |
| test |
+----------------------+
18 rows in set (0.21 sec)

mysql> create new database practice;


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 'new
database practice' at line 1
mysql> create database practice;
Query OK, 1 row affected (0.03 sec)

mysql> show databases;


+----------------------+
| Database |
+----------------------+
| blogdb |
| creditcarddb |
| dac |
| dac1 |
| dairy |
| dairyworld |
| farmersystem |
| healthcare |
| hibernatemapping |
| information_schema |
| mysql |
| onlinefurniturestore |
| performance_schema |
| practice |
| shoppingdb |
| studentsdb |
| supriyaorder |
| sys |
| test |
+----------------------+
19 rows in set (0.00 sec)

mysql> use practice;


Database changed
mysql> describe ;
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 1
mysql> describe practice;
ERROR 1146 (42S02): Table 'practice.practice' doesn't exist
mysql> Create Table Employee
-> (
-> emp_id int,
-> fname varchar(10),
-> lname varchar(10),
-> salary int,
-> joining_date date,
-> department varchar(10)
->
->
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> describe employee;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| emp_id | int | YES | | NULL | |
| fname | varchar(10) | YES | | NULL | |
| lname | varchar(10) | YES | | NULL | |
| salary | int | YES | | NULL | |
| joining_date | date | YES | | NULL | |
| department | varchar(10) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
6 rows in set (0.02 sec)

mysql> Insert into Employee values(1,'Jhon','Abel',10000,'01-jan-13',Banking);


ERROR 1054 (42S22): Unknown column 'Banking' in 'field list'
mysql> Insert into Employee values(1,'Jhon','Abel',10000,'01-jan-13','Banking');
ERROR 1292 (22007): Incorrect date value: '01-jan-13' for column 'joining_date' at
row 1
mysql> Insert into Employee values(1,'Jhon','Abel',10000,'01-jan-10','Banking');
ERROR 1292 (22007): Incorrect date value: '01-jan-10' for column 'joining_date' at
row 1
mysql> Insert into Employee values(1,'Jhon','Abel',10000,'01-jan-0010','Banking');
ERROR 1292 (22007): Incorrect date value: '01-jan-0010' for column 'joining_date'
at row 1
mysql> Insert into Employee values(1,'Jhon','Abel',10000,'01-jan-2010','Banking');
ERROR 1292 (22007): Incorrect date value: '01-jan-2010' for column 'joining_date'
at row 1
mysql> Insert into Employee values(1,'Jhon','Abel',10000,01-jan-20100,'Banking');
ERROR 1054 (42S22): Unknown column 'jan' in 'field list'
mysql> Insert into Employee values(1,'Jhon','Abel',10000,01-jan-2010,'Banking');
ERROR 1054 (42S22): Unknown column 'jan' in 'field list'
mysql> Insert into Employee values(1,'Jhon','Abel',10000,'2010-jan-01','Banking');
ERROR 1292 (22007): Incorrect date value: '2010-jan-01' for column 'joining_date'
at row 1
mysql> Insert into Employee values(1,'Jhon','Abel',10000,'2010-01-01','Banking');
Query OK, 1 row affected (0.04 sec)

mysql> Insert into Employee values(2,'Michale','Cler',20000,'2013-01-


01','Insurance');
Query OK, 1 row affected (0.01 sec)

mysql> Insert into Employee values(3,'Roy','Thomas',90000,'2013-02-01','Banking');


Query OK, 1 row affected (0.00 sec)

mysql> Insert into Employee values(4,'Tom','Joss',80000,'2013-01-01','Insurance');


Query OK, 1 row affected (0.00 sec)

mysql> Insert into Employee values(5,'Jerry','Pinto',68000,'2013-01-


01','Services');
Query OK, 1 row affected (0.03 sec)

mysql> show employee;


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
'employee' at line 1
mysql> describe employee;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| emp_id | int | YES | | NULL | |
| fname | varchar(10) | YES | | NULL | |
| lname | varchar(10) | YES | | NULL | |
| salary | int | YES | | NULL | |
| joining_date | date | YES | | NULL | |
| department | varchar(10) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> select * from employee;


+--------+---------+--------+--------+--------------+------------+
| emp_id | fname | lname | salary | joining_date | department |
+--------+---------+--------+--------+--------------+------------+
| 1 | Jhon | Abel | 10000 | 2010-01-01 | Banking |
| 2 | Michale | Cler | 20000 | 2013-01-01 | Insurance |
| 3 | Roy | Thomas | 90000 | 2013-02-01 | Banking |
| 4 | Tom | Joss | 80000 | 2013-01-01 | Insurance |
| 5 | Jerry | Pinto | 68000 | 2013-01-01 | Services |
+--------+---------+--------+--------+--------------+------------+
5 rows in set (0.03 sec)

mysql> create table Incentive;


ERROR 4028 (HY000): A table must have at least one visible column.
mysql> create table Incentive(employee_ref_id 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 1
mysql> create table incentive
-> (
-> employee_ref_id int,
-> incentive date,
-> incentive_amount
->
-> );
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 7
mysql> create table incentive
-> (
-> employee_ref_id int,
-> incentive date,
-> incentive_amount 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 7
mysql> create table incentive
-> (
-> employee_ref_id int,
-> incentive date,
-> incentive_amount int
->
-> );
Query OK, 0 rows affected (0.06 sec)

mysql> select * from incentive;


Empty set (0.03 sec)
mysql> describe incentive;
+------------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------+------+-----+---------+-------+
| employee_ref_id | int | YES | | NULL | |
| incentive | date | YES | | NULL | |
| incentive_amount | int | YES | | NULL | |
+------------------+------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into incentive values(1,'2013-02-01',5000);


Query OK, 1 row affected (0.01 sec)

mysql> insert into incentive values(2,'2013-02-01',3000);


Query OK, 1 row affected (0.00 sec)

mysql> insert into incentive values(3,'2013-02-01',4000);


Query OK, 1 row affected (0.00 sec)

mysql> insert into incentive values(1,'2013-02-01',4500);


Query OK, 1 row affected (0.00 sec)

mysql> insert into incentive values(2,'2013-01-01',3500);


Query OK, 1 row affected (0.00 sec)

mysql> slect * from incentive;


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 'slect *
from incentive' at line 1
mysql> select * from incentive;
+-----------------+------------+------------------+
| employee_ref_id | incentive | incentive_amount |
+-----------------+------------+------------------+
| 1 | 2013-02-01 | 5000 |
| 2 | 2013-02-01 | 3000 |
| 3 | 2013-02-01 | 4000 |
| 1 | 2013-02-01 | 4500 |
| 2 | 2013-01-01 | 3500 |
+-----------------+------------+------------------+
5 rows in set (0.00 sec)

mysql> select * from employee;


+--------+---------+--------+--------+--------------+------------+
| emp_id | fname | lname | salary | joining_date | department |
+--------+---------+--------+--------+--------------+------------+
| 1 | Jhon | Abel | 10000 | 2010-01-01 | Banking |
| 2 | Michale | Cler | 20000 | 2013-01-01 | Insurance |
| 3 | Roy | Thomas | 90000 | 2013-02-01 | Banking |
| 4 | Tom | Joss | 80000 | 2013-01-01 | Insurance |
| 5 | Jerry | Pinto | 68000 | 2013-01-01 | Services |
+--------+---------+--------+--------+--------------+------------+
5 rows in set (0.00 sec)

mysql> select fname as emp_name from employee;


+----------+
| emp_name |
+----------+
| Jhon |
| Michale |
| Roy |
| Tom |
| Jerry |
+----------+
5 rows in set (0.00 sec)

mysql> select upper(fname) from employee;


+--------------+
| upper(fname) |
+--------------+
| JHON |
| MICHALE |
| ROY |
| TOM |
| JERRY |
+--------------+
5 rows in set (0.00 sec)

mysql> select Upper(fname) from employee;


+--------------+
| Upper(fname) |
+--------------+
| JHON |
| MICHALE |
| ROY |
| TOM |
| JERRY |
+--------------+
5 rows in set (0.00 sec)

mysql> select distinct department from employee;


+------------+
| department |
+------------+
| Banking |
| Insurance |
| Services |
+------------+
3 rows in set (0.01 sec)

mysql> select INstr('o','Jhon') as matchposition from employee;


+---------------+
| matchposition |
+---------------+
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
+---------------+
5 rows in set (0.01 sec)

mysql> select * from employee;


+--------+---------+--------+--------+--------------+------------+
| emp_id | fname | lname | salary | joining_date | department |
+--------+---------+--------+--------+--------------+------------+
| 1 | Jhon | Abel | 10000 | 2010-01-01 | Banking |
| 2 | Michale | Cler | 20000 | 2013-01-01 | Insurance |
| 3 | Roy | Thomas | 90000 | 2013-02-01 | Banking |
| 4 | Tom | Joss | 80000 | 2013-01-01 | Insurance |
| 5 | Jerry | Pinto | 68000 | 2013-01-01 | Services |
+--------+---------+--------+--------+--------------+------------+
5 rows in set (0.00 sec)

mysql> select substr(fname,0,3)from employee;


+-------------------+
| substr(fname,0,3) |
+-------------------+
| |
| |
| |
| |
| |
+-------------------+
5 rows in set (0.02 sec)

mysql> select * from employee;


+--------+---------+--------+--------+--------------+------------+
| emp_id | fname | lname | salary | joining_date | department |
+--------+---------+--------+--------+--------------+------------+
| 1 | Jhon | Abel | 10000 | 2010-01-01 | Banking |
| 2 | Michale | Cler | 20000 | 2013-01-01 | Insurance |
| 3 | Roy | Thomas | 90000 | 2013-02-01 | Banking |
| 4 | Tom | Joss | 80000 | 2013-01-01 | Insurance |
| 5 | Jerry | Pinto | 68000 | 2013-01-01 | Services |
+--------+---------+--------+--------+--------------+------------+
5 rows in set (0.00 sec)

mysql> select substr(fname,0,3) as thrrech from employee;


+---------+
| thrrech |
+---------+
| |
| |
| |
| |
| |
+---------+
5 rows in set (0.03 sec)

mysql> SELECT emp_id, LEFT(fname, 3) AS first_three_chars


-> FROM Employee;
+--------+-------------------+
| emp_id | first_three_chars |
+--------+-------------------+
| 1 | Jho |
| 2 | Mic |
| 3 | Roy |
| 4 | Tom |
| 5 | Jer |
+--------+-------------------+
5 rows in set (0.01 sec)

mysql> select left(fname,3) from employee;


+---------------+
| left(fname,3) |
+---------------+
| Jho |
| Mic |
| Roy |
| Tom |
| Jer |
+---------------+
5 rows in set (0.00 sec)

mysql> select substr(fname,3) from employee;


+-----------------+
| substr(fname,3) |
+-----------------+
| on |
| chale |
| y |
| m |
| rry |
+-----------------+
5 rows in set (0.00 sec)

mysql> select substr(0,3) from employee;


+-------------+
| substr(0,3) |
+-------------+
| |
| |
| |
| |
| |
+-------------+
5 rows in set (0.00 sec)

mysql> select substr(fname,1,3) from employee;


+-------------------+
| substr(fname,1,3) |
+-------------------+
| Jho |
| Mic |
| Roy |
| Tom |
| Jer |
+-------------------+
5 rows in set (0.00 sec)

mysql> select INstr(fname,'o') as position_of_o from employee where fname='jhon';


+---------------+
| position_of_o |
+---------------+
| 3 |
+---------------+
1 row in set (0.03 sec)

mysql> select fname, length(fname) from employee;


+---------+---------------+
| fname | length(fname) |
+---------+---------------+
| Jhon | 4 |
| Michale | 7 |
| Roy | 3 |
| Tom | 3 |
| Jerry | 5 |
+---------+---------------+
5 rows in set (0.03 sec)

mysql> select fname, length(fname) from employee where emp_id=1;


+-------+---------------+
| fname | length(fname) |
+-------+---------------+
| Jhon | 4 |
+-------+---------------+
1 row in set (0.03 sec)

mysql> select fname ||'_'|| lname from employee;


+---------------------+
| fname ||'_'|| lname |
+---------------------+
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
+---------------------+
5 rows in set, 13 warnings (0.01 sec)

mysql> select fname || '_' || lname from employee;


+-----------------------+
| fname || '_' || lname |
+-----------------------+
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
+-----------------------+
5 rows in set, 13 warnings (0.03 sec)

mysql> select fname,lname from employee;


+---------+--------+
| fname | lname |
+---------+--------+
| Jhon | Abel |
| Michale | Cler |
| Roy | Thomas |
| Tom | Joss |
| Jerry | Pinto |
+---------+--------+
5 rows in set (0.00 sec)

mysql> select fname,lname as fullname from employee;


+---------+----------+
| fname | fullname |
+---------+----------+
| Jhon | Abel |
| Michale | Cler |
| Roy | Thomas |
| Tom | Joss |
| Jerry | Pinto |
+---------+----------+
5 rows in set (0.00 sec)

mysql> select CONCAT(fname,'_',lname) as fullname from employee;


+--------------+
| fullname |
+--------------+
| Jhon_Abel |
| Michale_Cler |
| Roy_Thomas |
| Tom_Joss |
| Jerry_Pinto |
+--------------+
5 rows in set (0.01 sec)

mysql> select CONCAT(fname,' ',lname) as fullname from employee;


+--------------+
| fullname |
+--------------+
| Jhon Abel |
| Michale Cler |
| Roy Thomas |
| Tom Joss |
| Jerry Pinto |
+--------------+
5 rows in set (0.00 sec)

mysql> select * from employee where fname IN('jhon','roy','jerry');


+--------+-------+--------+--------+--------------+------------+
| emp_id | fname | lname | salary | joining_date | department |
+--------+-------+--------+--------+--------------+------------+
| 1 | Jhon | Abel | 10000 | 2010-01-01 | Banking |
| 3 | Roy | Thomas | 90000 | 2013-02-01 | Banking |
| 5 | Jerry | Pinto | 68000 | 2013-01-01 | Services |
+--------+-------+--------+--------+--------------+------------+
3 rows in set (0.00 sec)

mysql> select * from employee order by fname asc;


+--------+---------+--------+--------+--------------+------------+
| emp_id | fname | lname | salary | joining_date | department |
+--------+---------+--------+--------+--------------+------------+
| 5 | Jerry | Pinto | 68000 | 2013-01-01 | Services |
| 1 | Jhon | Abel | 10000 | 2010-01-01 | Banking |
| 2 | Michale | Cler | 20000 | 2013-01-01 | Insurance |
| 3 | Roy | Thomas | 90000 | 2013-02-01 | Banking |
| 4 | Tom | Joss | 80000 | 2013-01-01 | Insurance |
+--------+---------+--------+--------+--------------+------------+
5 rows in set (0.01 sec)

mysql> select * from employee order by fname;


+--------+---------+--------+--------+--------------+------------+
| emp_id | fname | lname | salary | joining_date | department |
+--------+---------+--------+--------+--------------+------------+
| 5 | Jerry | Pinto | 68000 | 2013-01-01 | Services |
| 1 | Jhon | Abel | 10000 | 2010-01-01 | Banking |
| 2 | Michale | Cler | 20000 | 2013-01-01 | Insurance |
| 3 | Roy | Thomas | 90000 | 2013-02-01 | Banking |
| 4 | Tom | Joss | 80000 | 2013-01-01 | Insurance |
+--------+---------+--------+--------+--------------+------------+
5 rows in set (0.00 sec)

mysql> select * from employee order by fname desc;


+--------+---------+--------+--------+--------------+------------+
| emp_id | fname | lname | salary | joining_date | department |
+--------+---------+--------+--------+--------------+------------+
| 4 | Tom | Joss | 80000 | 2013-01-01 | Insurance |
| 3 | Roy | Thomas | 90000 | 2013-02-01 | Banking |
| 2 | Michale | Cler | 20000 | 2013-01-01 | Insurance |
| 1 | Jhon | Abel | 10000 | 2010-01-01 | Banking |
| 5 | Jerry | Pinto | 68000 | 2013-01-01 | Services |
+--------+---------+--------+--------+--------------+------------+
5 rows in set (0.00 sec)

mysql> select * from employee where fname like '%o%';


+--------+-------+--------+--------+--------------+------------+
| emp_id | fname | lname | salary | joining_date | department |
+--------+-------+--------+--------+--------------+------------+
| 1 | Jhon | Abel | 10000 | 2010-01-01 | Banking |
| 3 | Roy | Thomas | 90000 | 2013-02-01 | Banking |
| 4 | Tom | Joss | 80000 | 2013-01-01 | Insurance |
+--------+-------+--------+--------+--------------+------------+
3 rows in set (0.01 sec)

mysql> select * from employee where fname like '%n';


+--------+-------+-------+--------+--------------+------------+
| emp_id | fname | lname | salary | joining_date | department |
+--------+-------+-------+--------+--------------+------------+
| 1 | Jhon | Abel | 10000 | 2010-01-01 | Banking |
+--------+-------+-------+--------+--------------+------------+
1 row in set (0.00 sec)

mysql> SELECT *
-> FROM Employee
-> WHERE YEAR(joining_date) = 2013;
+--------+---------+--------+--------+--------------+------------+
| emp_id | fname | lname | salary | joining_date | department |
+--------+---------+--------+--------+--------------+------------+
| 2 | Michale | Cler | 20000 | 2013-01-01 | Insurance |
| 3 | Roy | Thomas | 90000 | 2013-02-01 | Banking |
| 4 | Tom | Joss | 80000 | 2013-01-01 | Insurance |
| 5 | Jerry | Pinto | 68000 | 2013-01-01 | Services |
+--------+---------+--------+--------+--------------+------------+
4 rows in set (0.01 sec)

mysql> select * from employee where year(joining_date)=2013;


+--------+---------+--------+--------+--------------+------------+
| emp_id | fname | lname | salary | joining_date | department |
+--------+---------+--------+--------+--------------+------------+
| 2 | Michale | Cler | 20000 | 2013-01-01 | Insurance |
| 3 | Roy | Thomas | 90000 | 2013-02-01 | Banking |
| 4 | Tom | Joss | 80000 | 2013-01-01 | Insurance |
| 5 | Jerry | Pinto | 68000 | 2013-01-01 | Services |
+--------+---------+--------+--------+--------------+------------+
4 rows in set (0.00 sec)

mysql> select fname,incentive_datew,joining_date from employee A inner join


incentives B on A.employee=B.employee_ref_id
;

You might also like