MYSQL PRACTICALS 2022

 MYSQL PRACTICALS - 2022

Experiment - 1

Objectives: Table “Order” is shown below. Write commands in SQL for (i) to (iv) and output for (v) and (vi).

Questions:

(i) To display names of SalesPersons (without duplicates)

(ii) To list orderedID and respective OrderAmont in descending order of OrderAmount.

(iii) To count the number of orders booked by SalesPerson with names starting with ‘R’.

(iv) To list order ids, order dates and order amounts that were booked after 1^st September 2015.

(v) SELECT ORDERID, ORDERAMOUNT FROM ORDER WHERE ORDERAMOUNT BETWEEN 50000 AND 60000.

(vi) SELECT CONCAT (ORDERID, SALESPERSON), LENGTH(SALESPERSON) FROM ORDER;

Ø Solution:

(i) SELECT DISTINCT(SALESPERSON) FROM ORDERS ;

(ii) SELECT ORDERID, ORDERAMOUNT FROM ORDERS ORDER BY ORDERAMOUNT DESC ;

(iii) SELECT SALESPERSON, COUNT(SALESPERSON) FROM ORDERS WHERE SALESPERSON LIKE ‘R%’ GROUP BY SALESPERSON ;

(iv) SELECT ORDERID, ORDERDATE, ORDERAMOUNT FROM ORDERS WHERE ORDERDATE > ‘2015-09-01’ ;

(v) SELECT ORDERID, ORDERAMOUNT FROM ORDER WHERE ORDERAMOUNT BETWEEN 50000 AND 60000.

(vi) SELECT CONCAT (ORDERID, SALESPERSON), LENGTH(SALESPERSON) FROM ORDER;

OUTPUT

(i)

(ii)

(iii)

(iv)

(v)

(vi)

Experiment - 2

Objectives: Consider the table given below. Write commands in SQL for (i) to (viii) and output for (ix) and (x).

Table: Salesperson

Columns SID and DOB contains Sales Person ID and Data of Birth respectively.

Questions:

(i) Write the the data types of SID and DOB columns

(ii) Display names of salesperson and their Salaries who have salaries in the range 30000.00 to 40000.00.

(iii) To list Names, Phone numbers and DOB of Salespersons who were born before 1^st November 1992.

(iv) To display Name and Salaries of Salespersons in descending order of salary.

(v) To display areas in which Salespersons are working. Duplicate Areas should not be displayed.

(vi) To display SID, Names along with Salaries increased by 500. (Increase of Rs. 500 is only to be displayed and not to be updated in the table)

(vii) To display Area along with number of Salespersons working in the area.

(viii) To display Names of Salespersons who have the word ‘Kumar’ anywhere in their names.

(ix) SELECT NAME, LENGTH(NAME) FROM SALESPERSON;

(x) SELECT AREA, COUNT(*) FROM SALESPERSON GROUP BY AREA HAVING COUNT(*) > 1;

Solution:

(i)  SID - VARCHAR, DOB - DATE

(ii) SELECT NAME, SALARY FROM SALESPERSON WHERE SALARY BETWEEN 30000.00 AND 40000.00 ;

(iii) SELECT NAME, PHONE, DOB FROM SALESPERSON WHERE DOB > ‘1992-11-01’ ;

(iv) SELECT NAME, SALARY FROM SALESPERSON ORDER BY SALARY DESC;

(v) SELECT DISTINCT AREA FROM SALESPERSON ;

(vi) SELECT SID, NAME, (SALARY + 500) AS SALARY FROM SALESPERSON ;

(vii) SELECT AREA, COUNT (*) FROM SALESPERSON GROUP BY AREA ;

(viii) SELECT NAME FROM SALESPERSON WHERE NAME LIKE ‘%KUMAR%’ ;

(ix) SELECT NAME, LENGTH(NAME) FROM SALESPERSON;

(x) SELECT AREA, COUNT(*) FROM SALESPERSON GROUP BY AREA HAVING COUNT(*) > 1;

OUTPUT

(ii)

(iii)

(iv)

(v)

(vi)

(vii)

(viii)

(ix)

(x)

Experiment - 3

Objectives: Consider the table given below. Write the commands in SQL for (i) to (viii) and output for (ix) and (x).

Table: Infant

Questions:

(i) To display the details about the Cot.

(ii) To list the names of items and their unit price that have unit price less than 800 and discount more than 5%.

(iii) To list the names of items and their date of purchase that were purchased after 31^st December 2015.

(iv) To display the number of items that have more than 10% as discount.

(v) To display item code and unit price in decreasing order of unit price.

(vi) To increase the unit price of each item by 10% of their unit price.

(vii) To display the highest unit price of items.

(viii) To display the names of items that have ‘Baby’ anywhere in their item names.

(ix) SELECT MID(ITEM, 1,2) FROM INFANT;

(x) SELECT AVG(UNITPRICE) FROM INFANT WHERE DATEPURCHASE > ‘2015-01-01’ ;

Solution:

(i) SELECT * FROM INFANT WHERE ITEM = 'COT' ;

(ii) SELECT ITEM, UNITPRCE FROM INFANT WHERE UNITPRICE < 800 AND DISCOUNT > 5 ;

(iii) SELECT ITEM, DATEPURCHASE FROM INFANT WHERE DATEPURCHASE > '2015-12-31' ;

(iv) SELECT COUNT(*) 'ITEM' FROM INFANT WHERE DISCOUNT > 10 ;

(v) SELECT ITEMCODE, UNITPRICE FROM INFANT ORDER BY UNITPRICE DESC ;

(vi) UPDATE INFANT SET UNITPRICE = (UNITPRICE + (UNITPRICE*0.10)) ;

(vii) SELECT MAX(UNITPRICE) FROM INFANT ;

(viii) SELECT ITEM FROM INFANT WHERE ITEM LIKE '%BABY%' ;

(ix) SELECT MID(ITEM, 1,2) FROM INFANT ;

(x) SELECT AVG(UNITPRICE) FROM INFANT WHERE DATEPURCHASE > ‘2015-01-01’ ;

OUTPUT

(i)

(ii)

(iii)

(iv)

(v)

(vi)

(vii)

(viii)

(ix)

(x)

Experiment - 04

Objectives: Consider the table given below. Write the commands in SQL for (i) to (v).

Table: EMPL

Questions:

(i) Calculate average salary of all employees listed in table empl.

(ii) Count number of records in table empl.

(iii) Count number of jobs in table empl.

(iv) How many distinct jobs are listed in table empl?

(v) Display maximum salary from table empl.

Solution:

(i) SELECT AVG(SAL) "AVERAGE SALARY" FROM EMPL ;

(ii) SELECT COUNT(*) 'TOTAL RECORD' FROM EMPL ;

(iii) SELECT COUNT(JOB) "JOB COUNT" FROM EMPL ;

(iv) SELECT COUNT(DISTINCT JOB) 'DISTINCT JOB' FROM EMPL ;

(v) SELECT MAX(SAL) 'MAXIMUM SALARY' FROM EMPL ;

OUTPUT

(i)

(ii)

(iii)

(iv)

(v)

Experiment - 05

Objectives: Consider the following table named ‘Product’ showing details of products being sold in a grocery shop. Write the queries for que. (i) and output for the que(ii) to (v).

Table: PRODUCT

Questions:

(i) Write SQL query to display the total number of products manufactured be each manufacturer.

(ii) SELECT PNAME, AVG(UPRICE) FROM PRODUCT GROUP BY PNAME;

(iii) SELECT DISTINCT MANUFACTURER FROM PRODUCT;

(iv) SELECT COUNT(DISTINCT PNAME) FROM PRODUCT;

(v) SELECT PNAME, MAX(UPRICE), MIN(UPRICE) FROM PRODUCT GROUP BY PNAME;

Solution:

(i) SELECT MANUFACTURER, COUNT(PCODE) FROM PRODUCT GROUP BY MANUFACTURER ; 

(ii) SELECT PNAME, AVG(UPRICE) FROM PRODUCT GROUP BY PNAME;

(iii) SELECT DISTINCT MANUFACTURER FROM PRODUCT;

(iv) SELECT COUNT(DISTINCT PNAME) FROM PRODUCT;

(v) SELECT PNAME, MAX(UPRICE), MIN(UPRICE) FROM PRODUCT GROUP BY PNAME;

OUTPUT

(i)

(ii)

(iii)

(iv)

(v)

Experiment - 06

Objectives: Consider the following table named ‘EXAM' with details of marks. Write the command of MYSQL for (i) to (iv) and output for (v) to (vii).

Table: EXAM

Questions:

(i) To display all information of the students of humanities in descending order of percentage.

(ii) To display Adno, SName, Percentage and Stream of those students whose name is less than 6 characters long.

(iii) To add another column Bus_Fees with datatype and size as Decimal (8,2).

(iv) To increase percentage by 2% of all the Humanities students.

(v) SELECT COUNT(*) FROM EXAM ;

(vi) SELECT SNAME, PERCENTAGE FROM EXAM WHERE SNAME LIKE "N%" ;

(vii) SELECT ROUND(PERCENTAGE, 0) FROM EXAM WHERE ADNO = 'R005' ;

Solution:

(i) SELECT * FROM EXAM WHERE STREAM = 'HUMANITIES' ORDER BY PERCENTAGE DESC ;

(ii) SELECT ADNO, SNAME, PERCENTAGE, STREAM FROM EXAM WHERE LENGTH(SNAME) < 6 ;

(iii) ALTER TABLE EXAM ADD BUS_FEES DECIMAL(8,2) ;

(iv) UPDATE EXAM SET PERCENTAGE = PERCENTAGE + PERCENTAGE * 0.02 WHERE STREAM = 'HUMANITIES' ;

(v) SELECT COUNT(*) FROM EXAM ;

(vi) SELECT SNAME, PERCENTAGE FROM EXAM WHERE SNAME LIKE "N%" ;

(vii) SELECT ROUND(PERCENTAGE, 0) FROM EXAM WHERE ADNO = 'R005' ;

OUTPUT

(i)

(ii)

(iii)

(iv)

(v)

(vi)

(vii)

Experiment - 07

Objectives: Consider the table supplier given below. Write the commands in MYSQL for (i) to (iv).

Table: SUPPLIER

Questions:

(i) To display name of the products, whose Pname starts with ‘B’ in ascending order of Price.

(ii) To display Supplier code, product name and City of the products whose quantity is less than 150.

(iii) To count distinct City in the table.

(iv) To insert a new row in the table SUPPLIER

                                110, ‘Bournvita’, ‘ABC’, 170, ‘Delhi’, 40.00

Solution:

(i) SELECT PNAME FROM SUPPLIER WHERE PNAME LIKE 'B%' ORDER BY PRICE;

(ii) SELECT SCODE, PNAME, CITY FROM SUPPLIER WHERE QTY < 150 ;

(iii) SELECT COUNT(DISTINCT CITY) FROM SUPPLIER ;

(iv) INSERT INTO SUPPLIER VALUES(110, 'BOURNVITA', 'ABC', 170, 'DELHI', 40.00);

OUTPUT

(i)

(ii)

(iii)

(iv)

Experiment - 08

Objectives: Consider the table PERSONS given below. Write the commands in MYSQL for (i) to (iv) and output for (v) to (viii).

Table: PERSONS

Questions:

(i) Display the Surname, Firstname and City of people residing in Udhamwara city.

(ii) Display the Person Ids (PID), Cities and Pincode of persons in descending order of pincode.

(iii) Display the Firstname and City of all the females getting Basic Salaries above 40000.

(iv) Display Firstname and Basicsalary of all the persons whose first name start with ‘G’.

(v) SELECT SURNAME FROM PERSONS WHERE BASICSALARY >= 50000 ;

(vi) SELECT SUM(BASICSALARY) FROM PERSONS WHERE GENDER = ‘F’ ;

(vii) SELECT GENDER, MIN(BASICSALARY) FROM PERSONS GROUP BY GENDER ;

(viii) SELECT GENDER, COUNT(*) FROM PERSONS GROUP BY GENDERS ;

Solution:

(i) SELECT SURNAME, FIRSTNAME, CITY FROM PERSONS WHERE CITY = ‘UDHAMWARA’ ;

(ii) SELECT PID, CITY, PINCODE FROM PERSONS ORDER BY PINCODE DESC ;

(iii) SELECT FIRSTNAME, CITY FROM PERSONS WHERE GENDER = ‘F’ AND BASICSALARY > 40000 ;

(iv) SELECT FIRSTNAME, BASICSALARY FROM PERSON WHERE FIRSTNAME LIKE ‘G%’ ;

(v) SELECT SURNAME FROM PERSONS WHERE BASICSALARY >= 50000 ;

(vi) SELECT SUM(BASICSALARY) FROM PERSONS WHERE GENDER = ‘F’ ;

(vii) SELECT GENDER, MIN(BASICSALARY) FROM PERSONS GROUP BY GENDER ;

(viii) SELECT GENDER, COUNT(*) FROM PERSONS GROUP BY GENDER ;

OUTPUT

(i)

(ii)

(iii)

(iv)

(v)

(vi)

(vii)

(viii)

Experiment - 09

Objectives: Consider the table DOCTOR given below. Write the commands in MYSQL for (i) to (iv) and output for (v) to (viii).

Table : DOCTOR

Questions

(i) Display the names and dates of joining of doctors of Oncology department.

(ii) Display the names and salaries of doctors in descending order of salaries.

(iii) Display the names and salaries of all the females doctors who are getting salary above 50000.

(iv) Display names of each department along with total salary being given to doctors of that department.

(v) SELECT DEPARTMENT FROM DOCTOR WHERE SALARY > = 55000 ;

(vi) SELECT SUM(SALARY) FROM DOCTORS WHERE DEPARTMENT = 'SURGERY' ;

(vii) SELECT DOC_NAME FROM DOCTOR WHERE DOC_NAME LIKE 'J%' ;

Solutions

(i) SELECT DOC_NAME, DATE_OF_JOIN FROM DOCTOR WHERE DEPARTMENT = 'ONCOLOGY' ;

(ii) SELECT DOC_NAME, SALARY FROM DOCTOR ORDER BY SALARY DESC;

(iii) SELECT DOC_NAME, SALARY FROM DOCTOR WHERE GENDER = 'F' AND SALARY > 50000 ;

(iv) SELECT DEPARTMENT, SUM(SALARY) FROM DOCTOR GROUP BY DEPARTMENT ;

(v) SELECT DEPARTMENT FROM DOCTOR WHERE SALARY > = 55000 ;

(vi) SELECT SUM(SALARY) FROM DOCTORS WHERE DEPARTMENT = 'SURGERY' ;

(vii) SELECT DOC_NAME FROM DOCTOR WHERE DOC_NAME LIKE 'J%' ;

OUTPUT

(i)

(ii)

Experiment - 10

Objectives: Consider the table LIBRARY given below. Write the commands in MYSQL for (i) to (iv) and output for (v) to (viii).

Table : LIBRARY

Questions

(i) To list the BookTitle of FND type.

(ii) To display a report listing BookTitle, Type and Price in descending order of price.

(iii) To count the number of BooksTitle, who have FND type.

(iv) To insert a new row in the table LIBRARY.

                7, 'Windows 8 Basics', 'FND', 'McGraw', 7, 150 ;

(v) SELECT AVG(PRICE) FROM LIBRARY WHERE TYPE = 'FND' ;

(vi) SELECT COUNT(DISTINCT TYPE) FROM LIBRARY ;

(vii) SELECT MIN(PRICE) FROM LIBRARY WHERE TYPE = 'FDN' ;

Solution:

(i) SELECT BOOKTITLE FROM LIBRARY WHERE TYPE = 'FND'

(ii) SELECT BOOKTITLE, TYPE, PRICE FROM LIBRARY ORDER BY PRICE DESC ;

(iii) SELECT COUNT(*) FROM LIBRARY WHERE TYPE = 'FND' ;

(iv) INSERT INTO LIBRARY VALUES( 7, 'WINDOWS 8 BASICS', 'FND', 'McGraw', 7, 150) ;

(v) SELECT AVG(PRICE) FROM LIBRARY WHERE TYPE = 'FND' ;

(vi) SELECT COUNT(DISTINCT TYPE) FROM LIBRARY ;

(vii) SELECT MIN(PRICE) FROM LIBRARY WHERE TYPE = 'FND' ;

OUTPUT

(i)

(ii)

===========================

EMERGING TRENDS

Emerging Trends

Introduction

Computers have been around for quite some time now. New technologies and initiatives emerge with each passing day. In order to understand the existing technologies and have a better view of the developments around us, we must keep an eye on the emerging trends.

Artificial Intelligence (AI)

Artificial Intelligence basically refers to the ability of a machine or a computer program to think and learn. In simple words, field of AI revolves around bringing out technologies that help build machines that can think, act, and learn like humans.

Many famous example of AI today is social humanoid robot Sophia, who has been awarded citizenship of Saudi Arabia.

Immersive Experience with Extended Reality (XR)

Extended Reality (XR) is an umbrella term that covers all the various technologies that enhance our senses and gives us immersive experience. It includes technologies Virtual Reality (VR), Augmented Reality (AR) and Mixed Reality (MR) technologies.

Virtual Reality (VR)

virtual reality (VR), the use of computer modeling and simulation that enables a person to interact with an artificial three-dimensional (3-D) visual or other sensory environment.

It is a technology that allows people to experience and interact with own senses. In a 3D virtual environment that appears and feels like a real environment with the use of an electronic equipment.

 Augmented Reality (AR)

The Augmented Reality (AR) is a technology that transforms the view of physical real-world environment with superimposed computer generated images, thus changing the perception of reality.

 Mixed Reality (MR)

A combined technology that makes use of both Augmented Reality (AR) as well as Virtual Reality (VR) is called Mixed Reality.

 Machine Learning (ML)

Machine Learning is an application of artificial intelligence (AI) that provides systems the ability to automatically learn and improve from experience without being explicitly programmed, after initial trainings using test and training data and algorithms.

Natural Language Processing (NLP)

Natural Language Processing is an artificially intelligent  technology that helps computer understand, interpret and manipulate human language and even potentially generate human language.

A few examples of NLP:

Spell check

Autocomplete

Voice text messaging

Spam Mail Filter

Text to speech

Machine Translation etc.

Robotics

Robotics are programmable machines that are usually able to carry out a series of actions autonomously, or semi-autonomously.

Robotic is a branch of technology that deals with physical robots.

Big Data

It is a term used to describe extremely large data sets that traditional databases applications cannot deal with. Big data mainly was used as a term to refer to the size and complexity of data sets.

Characteristics of Big Data

Volume- It refers to the enormous amount of data generated.

Variety- It refers to the number of type of data generated.

Velocity- It is the measure of how fat data is coming in.

Veracity- Data Veracity is the degree of accuracy or truthfulness of a data set.

Value- Big data must be stored, processed and analyzed in a way so that it gets converted into something valuable and produces some real valuable Information.

Data Analytics

data analytics refers to discovering hidden patterns,trends and preferences, from the data set by employing specialized methods, programs, software and specialized systems. 

Internet of Things (IOT)

The IOT is a new age technology that allows computing devices to transfer data over a network like internet without requiring human to human or human to computer interaction.

Health and fitness- IOT smart gadgets like fitbit, jawbone, nike and misft,etc. tjat monitor your heart rate blood pressure etc.

Home Security- There are many home safety and security devices for everyone that enable video surveillance, motion, temperature and air quantity control to help you protect your family and your home when you are not around.

Transport- Driverless cars can not only drive on road without drivers but also can be in touch with servers all the time.

Shopping-There are smart refrigerators nowdays that can order for grocery items as soon their quantity in frigde goes below a set level.

Smart Cities-The IOT Technology is main enabler of making 'Smart City' a reality. Smart surveillance and environmental monitoring etc., examples of Internet of Things application for smart cities.

Web of Things (WoT)

The Web of Things is framework with a vision where everyday's smart devices and objects, are connected the Web using existing Web technologies and standards.

Example of smart devices and objects are Wireless Sensor Networks, Ambient devices, smartphones, smart household appliances kike smart TVs, smart refrigerators, smart watches etc.

Sensors: Sensors are devices which measure some physical energy (like temperature or change in director etc.) and convert it into some type of signals.

Different types of sensors: Temperature sensor, Ultrasonic sensor, Smoke, Gas and Alcohol sensor, Light Sensor, Proximity sensor, Humidity Sensor, Touch sensor, Gyroscope sensor etc.

Smart Cities: A smart city is a city that uses technology to provide services and solve city problems. A smart city does thing like: improved transportation and accessibility, improved social services, improved quality and sustainability. The main goals of a smart city are to improve policy efficiency, reduce waste and inconvenience, improve social and economic quality, and maximize social inclusion.

Cloud Computing: Cloud computing is the Internet-based computing, whereby shared resources, software, and information are provided to computers and other devices on demand, like the electricity grid. A basic definition of cloud computing is the use of the Internet for the tasks you perform on your computer for storage, retrieval and access. The 'cloud' represents the Internet. Cloud computing is a new name for and old concept: the delivery of computing services from a remote location.

Cloud computing has following essential characteristics:

1. On demand self service

2. Rapid elasticity

3. Broad network access

4 Measured service etc

Example: Google Drive, iCloud, One Drive, Microsoft Azure etc.

Cloud Service: Using clouds, many types of services are offered, accessible through clients.

Common Types of cloud services

(i) Software as a Service (SaaS): In this type of service, a complete application is offered to the customer, as a service on demand. With SaaS, you don't have to worry about the installation, setup and running of the application. Service provider will do that for you. You just have to pay and use it through some client.

Examples: Google Apps, Microsoft Office 365, Zoho CRM, Zoho One, Salesforce Customer 360 etc.

(ii) Platform as a Service (Paas): In this type of service, a development environment is offered as a service, upon which other higher levels of service can be built. PaaS provides the computing platforms which typically include operating system, programming language execution environment, database, web server and so forth.

Examples: AWS Elastic, Beanstalk, Windows Azure, Heroku, Force.com, Google App Engine, Apache Stratos.

(iii) Infrastructure as a Service(IaaS): It provides basic storage and computing capabilities as standardized services over the network. IaaS provides the computing infrastructure, physical or virtual machines like servers, and other resources like virtual-machine disk image library, block and file-based storage, firewalls, load balancers, IP addresses, virtual local area network and so forth.

Examples: Amazon EC2, Windows Azure, Rackspace, Google Compute Engine etc.

Types of Clouds: There are different types of clouds that you can subscribe to depending on your needs,

1. Private Clouds: A private cloud consists of computing resources used exclusively owned by one business or organization. In a private cloud, the services and infrastructure are always maintained on private network and the hardware and software are dedicated solely to one organization.

Private clouds are often used by government agencies, financial institution, any other mid-to large size organizations.

2. Public Clouds: Public cloud refers to a common cloud service made available to multiple subscribers. The cloud resource are owned and operated by a third-party cloud service provider and delivered over the Internet.

Public cloud deployments are frequently used to provide web-based email, online office applications, storage and testing and development environments.

Example: iCloud, Amazon Cloud Drive, Google Drive etc.

Grid Computing: Grid computing is a distributed computing system formed by a network of independent heterogeneous computers in multiple locations. Grid computing links disparate, low-cost computers into one large infrastructure, harnessing their unused processing and other compute resources.

The two common types of Grids are:

(i) Computational Grid (Compute Grid): Compute grids allow you to take a computation, optionally split it into multiple parts, and execute them on different grid nodes in parallel. Some Compute Grid vendors are - GridGain (a professional Open Source), JPPF (Open Source).

(ii) Data Grid: Data grids provide an infrastructure to support data storage, data discovery, data handling, data publication, and data manipulation of large volumes of data actually stored in various heterogeneous database and file systems. Some Data Grid vendors are - JBossCache (Professional Open Source), EhCache (Open Source).

Blockchain Technology: 

Q. What does blockchain technology do?

Ans:

To be continued.......