[go: up one dir, main page]
Scribd
Upload
26 views9 pages

Advsql Functions

The document demonstrates various SQL queries performed on sample customer and orders tables. It creates the tables, inserts data, and demonstrates different types of joins - inner join, left outer join, right outer join, cross join and IN clause.

Uploaded by

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

Advsql Functions

The document demonstrates various SQL queries performed on sample customer and orders tables. It creates the tables, inserts data, and demonstrates different types of joins - inner join, left outer join, right outer join, cross join and IN clause.

Uploaded by

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

mysql> create database berlin;

Query OK, 1 row affected (0.10 sec)

mysql> use berlin;


Database changed

Q1. CREATE TABLE CUSTOMER WITH ATTRIBUTES CUSTOMER_ID, NAME, OCCUPATION


AND AGE.

mysql> create table customer(customer_id int(10), customer_name


varchar(25), occupation varchar(30), customer_age int(10));
Query OK, 0 rows affected, 2 warnings (0.27 sec)

mysql> describe customer;


+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| customer_id | int | YES | | NULL | |
| customer_name | varchar(25) | YES | | NULL | |
| occupation | varchar(30) | YES | | NULL | |
| customer_age | int | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+

Q2. INSERT 3 RECORDS IN CUSTOMER TABLE.

mysql> insert into customer values(1001, 'John', 'Manager', 28);


Query OK, 1 row affected (0.04 sec)

mysql> insert into customer values(1002, 'Tanvi', 'Chartered Accountant',


32);
Query OK, 1 row affected (0.05 sec)

mysql> insert into customer values(1003, 'Sanjana', 'Web Developer', 30);


Query OK, 1 row affected (0.05 sec)

mysql> select * from customer;


+-------------+---------------+----------------------+--------------+
| customer_id | customer_name | occupation | customer_age |
+-------------+---------------+----------------------+--------------+
| 1001 | John | Manager | 28 |
| 1002 | Tanvi | Chartered Accountant | 32 |
| 1003 | Sanjana | Web Developer | 30 |
+-------------+---------------+----------------------+--------------+

Q3. CREATE TABLE ORDERS WITH ATTRIBUTES ORDER_ID, CUSTOMER_ID, PRODUCT


NAME AND ORDER DATE.

mysql> create table orders(order_id int(10), customer_id int(10),


product_name varchar(30), order_date date);
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| order_id | int | YES | | NULL | |
| customer_id | int | YES | | NULL | |
| product_name | varchar(30) | YES | | NULL | |
| order_date | date | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
Q4. INSERT 5 RECORDS IN ORDERS TABLE.

mysql> insert into orders values(2401, 1001, 'Headphones', '2024-02-02');


Query OK, 1 row affected (0.01 sec)

mysql> insert into orders values(2402, 1002, 'Denim Jacket', '2024-03-


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

mysql> insert into orders values(2403, 1003, 'Shoes', '2024-01-25');


Query OK, 1 row affected (0.00 sec)

mysql> insert into orders values(2404, 1004, 'Earrings', '2024-01-15');


Query OK, 1 row affected (0.01 sec)

mysql> insert into orders values(2405, 1005, 'Smart Watch', '2024-02-


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

mysql> select * from orders;


+----------+-------------+--------------+------------+
| order_id | customer_id | product_name | order_date |
+----------+-------------+--------------+------------+
| 2401 | 1001 | Headphones | 2024-02-02 |
| 2402 | 1002 | Denim Jacket | 2024-03-21 |
| 2403 | 1003 | Shoes | 2024-01-25 |
| 2404 | 1004 | Earrings | 2024-01-15 |
| 2405 | 1005 | Smart Watch | 2024-02-28 |
+----------+-------------+--------------+------------+

******JOIN FUNCIONS******

Q. SHOW THE USE INNER JOIN ON TWO TABLES.

mysql> SELECT CUSTOMER_NAME, ORDER_ID, ORDER_DATE FROM CUSTOMER INNER


JOIN ORDERS ON CUSTOMER.CUSTOMER_ID=ORDERS.CUSTOMER_ID;
+---------------+----------+------------+
| CUSTOMER_NAME | ORDER_ID | ORDER_DATE |
+---------------+----------+------------+
| John | 2401 | 2024-02-02 |
| Tanvi | 2402 | 2024-03-21 |
| Sanjana | 2403 | 2024-01-25 |
+---------------+----------+------------+

Q. SHOW THE USE OF LEFT OUTER JOIN ON TWO TABLES.

mysql> SELECT * FROM CUSTOMER left outer JOIN ORDERS ON


CUSTOMER.CUSTOMER_ID=ORDERS.CUSTOMER_ID;
+-------------+---------------+----------------------+--------------+----
------+-------------+--------------+------------+
| customer_id | customer_name | occupation | customer_age |
order_id | customer_id | product_name | order_date |
+-------------+---------------+----------------------+--------------+----
------+-------------+--------------+------------+
| 1001 | John | Manager | 28 |
2401 | 1001 | Headphones | 2024-02-02 |
| 1002 | Tanvi | Chartered Accountant | 32 |
2402 | 1002 | Denim Jacket | 2024-03-21 |
| 1003 | Sanjana | Web Developer | 30 |
2403 | 1003 | Shoes | 2024-01-25 |
| 1004 | Aman | Chef | 30 |
NULL | NULL | NULL | NULL |
+-------------+---------------+----------------------+--------------+----
------+-------------+--------------+------------+

Q. SHOW THE USE OF RIGHT OUTER JOIN ON TWO TABLES.

mysql> SELECT * FROM CUSTOMER right outer JOIN ORDERS ON


CUSTOMER.CUSTOMER_ID=ORDERS.CUSTOMER_ID;
+-------------+---------------+----------------------+--------------+----
------+-------------+--------------+------------+
| customer_id | customer_name | occupation | customer_age |
order_id | customer_id | product_name | order_date |
+-------------+---------------+----------------------+--------------+----
------+-------------+--------------+------------+
| 1001 | John | Manager | 28 |
2401 | 1001 | Headphones | 2024-02-02 |
| 1002 | Tanvi | Chartered Accountant | 32 |
2402 | 1002 | Denim Jacket | 2024-03-21 |
| 1003 | Sanjana | Web Developer | 30 |
2403 | 1003 | Shoes | 2024-01-25 |
| 1004 | Aman | Chef | 30 |
2404 | 1004 | Earrings | 2024-01-15 |
| NULL | NULL | NULL | NULL |
2405 | 1005 | Smart Watch | 2024-02-28 |
+-------------+---------------+----------------------+--------------+----
------+-------------+--------------+------------+

Q.SHOW THE USE OF CROSS JOIN ON TWO TABLES.

mysql> select * from customer cross join orders;


+-------------+---------------+----------------------+--------------+----
------+-------------+--------------+------------+
| customer_id | customer_name | occupation | customer_age |
order_id | customer_id | product_name | order_date |
+-------------+---------------+----------------------+--------------+----
------+-------------+--------------+------------+
| 1004 | Aman | Chef | 30 |
2401 | 1001 | Headphones | 2024-02-02 |
| 1003 | Sanjana | Web Developer | 30 |
2401 | 1001 | Headphones | 2024-02-02 |
| 1002 | Tanvi | Chartered Accountant | 32 |
2401 | 1001 | Headphones | 2024-02-02 |
| 1001 | John | Manager | 28 |
2401 | 1001 | Headphones | 2024-02-02 |
| 1004 | Aman | Chef | 30 |
2402 | 1002 | Denim Jacket | 2024-03-21 |
| 1003 | Sanjana | Web Developer | 30 |
2402 | 1002 | Denim Jacket | 2024-03-21 |
| 1002 | Tanvi | Chartered Accountant | 32 |
2402 | 1002 | Denim Jacket | 2024-03-21 |
| 1001 | John | Manager | 28 |
2402 | 1002 | Denim Jacket | 2024-03-21 |
| 1004 | Aman | Chef | 30 |
2403 | 1003 | Shoes | 2024-01-25 |
| 1003 | Sanjana | Web Developer | 30 |
2403 | 1003 | Shoes | 2024-01-25 |
| 1002 | Tanvi | Chartered Accountant | 32 |
2403 | 1003 | Shoes | 2024-01-25 |
| 1001 | John | Manager | 28 |
2403 | 1003 | Shoes | 2024-01-25 |
| 1004 | Aman | Chef | 30 |
2404 | 1004 | Earrings | 2024-01-15 |
| 1003 | Sanjana | Web Developer | 30 |
2404 | 1004 | Earrings | 2024-01-15 |
| 1002 | Tanvi | Chartered Accountant | 32 |
2404 | 1004 | Earrings | 2024-01-15 |
| 1001 | John | Manager | 28 |
2404 | 1004 | Earrings | 2024-01-15 |
| 1004 | Aman | Chef | 30 |
2405 | 1005 | Smart Watch | 2024-02-28 |
| 1003 | Sanjana | Web Developer | 30 |
2405 | 1005 | Smart Watch | 2024-02-28 |
| 1002 | Tanvi | Chartered Accountant | 32 |
2405 | 1005 | Smart Watch | 2024-02-28 |
| 1001 | John | Manager | 28 |
2405 | 1005 | Smart Watch | 2024-02-28 |
+-------------+---------------+----------------------+--------------+----
------+-------------+--------------+------------+
20 rows in set (0.00 sec)

Q.SHOW THE USE OF INCLAUSE ON TWO TABLES.

mysql> select * from customer where customer_age IN(select customer_age


from customer where customer_id>2);
+-------------+---------------+----------------------+--------------+
| customer_id | customer_name | occupation | customer_age |
+-------------+---------------+----------------------+--------------+
| 1001 | John | Manager | 28 |
| 1002 | Tanvi | Chartered Accountant | 32 |
| 1003 | Sanjana | Web Developer | 30 |
| 1004 | Aman | Chef | 30 |
+-------------+---------------+----------------------+--------------+
4 rows in set (0.00 sec)

Q.SHOW THE USE OF EXIST CLAUSE ON TWO TABLES.

mysql> select customer_name, occupation from customer where EXISTS(select


* from orders where customer.customer_id=orders.customer_id);
+---------------+----------------------+
| customer_name | occupation |
+---------------+----------------------+
| John | Manager |
| Tanvi | Chartered Accountant |
| Sanjana | Web Developer |
| Aman | Chef |
+---------------+----------------------+
4 rows in set (0.00 sec)

******MATH FUNCTIONS******
mysql> SELECT ABS(5);
+--------+
| ABS(5) |
+--------+
| 5 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT ABS(-5);


+---------+
| ABS(-5) |
+---------+
| 5 |
+---------+
1 row in set (0.02 sec)

mysql> select ceil(1.33);


+------------+
| ceil(1.33) |
+------------+
| 2 |
+------------+

mysql> select floor(1.33);


+-------------+
| floor(1.33) |
+-------------+
| 1 |
+-------------+

mysql> select log(2);


+--------------------+
| log(2) |
+--------------------+
| 0.6931471805599453 |
+--------------------+
1 row in set (0.00 sec)

mysql> select log10(2);


+--------------------+
| log10(2) |
+--------------------+
| 0.3010299956639812 |
+--------------------+
1 row in set (0.00 sec)

mysql> select 336%10;


+--------+
| 336%10 |
+--------+
| 6 |
+--------+

mysql> select power(2,3);


+------------+
| power(2,3) |
+------------+
| 8 |
+------------+
1 row in set (0.00 sec)

mysql> select sqrt(16);


+----------+
| sqrt(16) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)

mysql> select sin(30);


+---------------------+
| sin(30) |
+---------------------+
| -0.9880316240928618 |
+---------------------+
1 row in set (0.00 sec)

mysql> select cos(30);


+---------------------+
| cos(30) |
+---------------------+
| 0.15425144988758405 |
+---------------------+
1 row in set (0.00 sec)

mysql> select tan(30);


+--------------------+
| tan(30) |
+--------------------+
| -6.405331196646276 |
+--------------------+
1 row in set (0.02 sec)

mysql> select cot(30);


+----------------------+
| cot(30) |
+----------------------+
| -0.15611995216165922 |
+----------------------+

mysql> select COALESCE(30);


+--------------+
| COALESCE(30) |
+--------------+
| 30 |
+--------------+

******ADVANCED SQL FUNCTIONS******

mysql> use university;


Database changed

mysql> show tables;


+----------------------+
| Tables_in_university |
+----------------------+
| department |
| employee |
+----------------------+
2 rows in set (0.00 sec)

mysql> select * from department;


+---------+-------------+-----------+
| dept_no | dept_name | dept_loc |
+---------+-------------+-----------+
| 1 | Designing | Delhi |
| 2 | Internship | Mumbai |
| 3 | Development | Bangalore |
+---------+-------------+-----------+
3 rows in set (0.02 sec)

mysql> select * from employee;


+--------+----------+------------------+-----------------+------------+--
----------+
| emp_no | emp_name | emp_jobtitle | emp_joiningdate | emp_salary |
emp_deptno |
+--------+----------+------------------+-----------------+------------+--
----------+
| 1 | Vishnu | Web Developer | 2013-05-06 | 80000 |
1 |
| 2 | Kailash | Graphic Designer | 2012-05-04 | 60000 |
3 |
| 3 | Megha | Data Analyst | 2014-10-03 | 40000 |
2 |
| 4 | Aman | Intern | 2014-10-03 | 45000 |
4 |
| 5 | Varada | Intern | 2014-09-07 | 55000 |
4 |
| 6 | Sania | AI Trainer | 2013-11-07 | 55000 |
5 |
| 7 | Dhruv | Graphic Designer | 2013-02-06 | 25000 |
3 |
| 8 | Shaurya | Intern | 2014-04-05 | 35000 |
4 |
| 9 | Kabir | AI Trainer | 2014-04-05 | 60000 |
5 |
| 10 | Shraddha | Web Designer | 2014-01-02 | 60000 |
6 |
+--------+----------+------------------+-----------------+------------+--
----------+
10 rows in set (0.01 sec)

Q1. DISPLAY EMPLOYEE ABC, emp_no,emp_name, emp_deptno and dept_loc.

mysql> select emp_no, emp_name, emp_deptno, dept_loc from


employee,department where employee.emp_deptno=department.dept_no;
+--------+----------+------------+-----------+
| emp_no | emp_name | emp_deptno | dept_loc |
+--------+----------+------------+-----------+
| 1 | Vishnu | 1 | Delhi |
| 2 | Kailash | 3 | Bangalore |
| 3 | Megha | 2 | Mumbai |
| 7 | Dhruv | 3 | Bangalore |
+--------+----------+------------+-----------+
4 rows in set (0.00 sec)

Q2. DISPLAY THE INFORMATION OF EMPLOYEES AND DEPARTMENT WHERE DEPARTMENT


IS NOT "INTERN".

mysql> select emp_no, emp_name, employee.emp_deptno, dept_loc from


employee,department where employee.emp_deptno=department.dept_no and
dept_name!='intern';
+--------+----------+------------+-----------+
| emp_no | emp_name | emp_deptno | dept_loc |
+--------+----------+------------+-----------+
| 1 | Vishnu | 1 | Delhi |
| 2 | Kailash | 3 | Bangalore |
| 3 | Megha | 2 | Mumbai |
| 7 | Dhruv | 3 | Bangalore |
+--------+----------+------------+-----------+
4 rows in set (0.00 sec)

Q3. DISPLAY DETAILS OF EMPLOYEES WHOSE JOBTITLE IS SAME AS THAT OF JOB


TITLE OF EMP_NO 2.

mysql> select * from employee where emp_jobtitle=(select emp_jobtitle


from employee where emp_no=2);
+--------+----------+------------------+-----------------+------------+--
----------+
| emp_no | emp_name | emp_jobtitle | emp_joiningdate | emp_salary |
emp_deptno |
+--------+----------+------------------+-----------------+------------+--
----------+
| 2 | Kailash | Graphic Designer | 2012-05-04 | 60000 |
3 |
| 7 | Dhruv | Graphic Designer | 2013-02-06 | 25000 |
3 |
+--------+----------+------------------+-----------------+------------+--
----------+
2 rows in set (0.01 sec)

Q4. DISPLAY EMPLOYEE DETAILS WHOSE SALARY IS MAXIMUM.

mysql> SELECT * FROM EMPLOYEE WHERE emp_salary=(SELECT MAX(emp_salary)


FROM EMPLOYEE);
+--------+----------+---------------+-----------------+------------+-----
-------+
| emp_no | emp_name | emp_jobtitle | emp_joiningdate | emp_salary |
emp_deptno |
+--------+----------+---------------+-----------------+------------+-----
-------+
| 1 | Vishnu | Web Developer | 2013-05-06 | 80000 |
1 |
+--------+----------+---------------+-----------------+------------+-----
-------+
1 row in set (0.01 sec)

mysql> SELECT * FROM EMPLOYEE WHERE emp_salary=(SELECT MIN(emp_salary)


FROM EMPLOYEE);
+--------+----------+------------------+-----------------+------------+--
----------+
| emp_no | emp_name | emp_jobtitle | emp_joiningdate | emp_salary |
emp_deptno |
+--------+----------+------------------+-----------------+------------+--
----------+
| 7 | Dhruv | Graphic Designer | 2013-02-06 | 25000 |
3 |
+--------+----------+------------------+-----------------+------------+--
----------+
1 row in set (0.00 sec)

Q5. DISPLAY ALL DEPARTMENTS THAT HAVE MINIMUM SALARY GREATER THAN THAT OF
DEPARTMENT NO 2.

mysql> SELECT emp_deptno,MIN(emp_salary) FROM EMPLOYEE GROUP BY


emp_deptno HAVING MIN(emp_salary)<(SELECT MIN(emp_salary) FROM EMPLOYEE
WHERE emp_deptno=2);
+------------+-----------------+
| emp_deptno | MIN(emp_salary) |
+------------+-----------------+
| 3 | 25000 |
| 4 | 35000 |
+------------+-----------------+
2 rows in set (0.01 sec)

Q6. FIND THE EMPLOYEE WHO EARNS SAME SALARY AS MINIMUM SALARY FOR THAT
DEPARTMENT.

mysql> SELECT emp_no, emp_name, emp_salary, emp_deptno FROM EMPLOYEE


WHERE emp_salary IN(SELECT MIN(emp_salary) FROM EMPLOYEE GROUP BY
emp_deptno);
+--------+----------+------------+------------+
| emp_no | emp_name | emp_salary | emp_deptno |
+--------+----------+------------+------------+
| 1 | Vishnu | 80000 | 1 |
| 2 | Kailash | 60000 | 3 |
| 3 | Megha | 40000 | 2 |
| 5 | Varada | 55000 | 4 |
| 6 | Sania | 55000 | 5 |
| 7 | Dhruv | 25000 | 3 |
| 8 | Shaurya | 35000 | 4 |
| 9 | Kabir | 60000 | 5 |
| 10 | Shraddha | 60000 | 6 |
+--------+----------+------------+------------+
9 rows in set (0.04 sec)

You might also like