SQL SELECT statement

                                                             SELECT FROM

SELECT - FROM - exercise

Select the information from the “dept_no” column of the “departments” table.

Select all data from the “departments” table. 

 
SELECT - FROM - solution

SELECT

    dept_no

FROM

    departments;

 

SELECT

    *

FROM

    departments;

                                                WHERE clause 

 It will allow us to set  a condition upon which we  will specify what part  of the  data we want to retrieve from the DB.


WHERE - exercise

Select all people from the “employees” table whose first name is “Elvis”. 😊

 

WHERE - solution

SELECT

    *

FROM

    employees

WHERE

    first_name = 'Elvis';

 

                                                AND operator 


AND : Allow you to logically combine two statements in the condition code block. Allow us to narrow the output we would like to extract from our data.

 

AND - exercise

Retrieve a list with all female employees whose first name is Kellie. 

 

AND - solution

SELECT

    *

FROM

    employees

WHERE

    first_name = 'Kellie' AND gender = 'F';  

 

                                          OR Operator 



OR - exercise

Retrieve a list with all employees whose first name is either Kellie or Aruna.

 

OR - solution

SELECT

    *

FROM

    employees

WHERE

    first_name = 'Kellie' OR first_name = 'Aruna'; 

 

                                         Operator precedence 


 

Operator precedence - exercise

Retrieve a list with all female employees whose first name is either Kellie or Aruna.

 

Operator precedence - solution

SELECT

    *

FROM

    employees

WHERE

    gender = 'F' AND (first_name = 'Kellie' OR first_name = 'Aruna');

 

                                      IN - NOT IN Operator

IN : The IN operator allows SQL to return the names written in parentheses, if they exists in our table.

NOT IN : This is the exact opposite to the IN Operator.

 

IN - NOT IN - exercise 1

Use the IN operator to select all individuals from the “employees” table, whose first name is either “Denis”, or “Elvis”.

 

IN - NOT IN - solution 1

SELECT

    *

FROM

    employees

WHERE

    first_name IN ('Denis' , 'Elvis');

 

IN - NOT IN - exercise 2
Extract all records from the ‘employees’ table, aside from those with employees named John, Mark, or Jacob.
 
 
IN - NOT IN - solution 2

SELECT

    *

FROM

    employees

WHERE

    first_name NOT IN ('John' , 'Mark', 'Jacob');

 
 
                                                 LIKE OPERATOR


%     -      A substitute for sequence of characters.
_     -     Helps you to match a single character.
 
 
LIKE - NOT LIKE - exercise

Working with the “employees” table, use the LIKE operator to select the data about all individuals, whose first name starts with “Mark”; specify that the name can be succeeded by any sequence of characters.

Retrieve a list with all employees who have been hired in the year 2000.

Retrieve a list with all employees whose employee number is written with 5 characters, and starts with “1000”.  

 

LIKE - NOT LIKE - solution

SELECT

    *

FROM

    employees

WHERE

    first_name LIKE('Mark%');

 

SELECT

    *

FROM

    employees

WHERE

    hire_date LIKE ('%2000%');

 

SELECT

    *

FROM

    employees

WHERE

    emp_no LIKE ('1000_');

 

                                        Wildcard characters 

 

 
Wildcard characters - exercise

Extract all individuals from the ‘employees’ table whose first name contains “Jack”.

Once you have done that, extract another list containing the names of employees that do not contain “Jack”. 

 

Wildcard characters - solution

SELECT

    *

FROM

    employees

WHERE

    first_name LIKE ('%JACK%');

SELECT

    *

FROM

    employees

WHERE

    first_name NOT LIKE ('%Jack%'); 

 

                                                BETWEEN...AND... 

        This help us to designate the interval to which a given value belongs.

 

BETWEEN - AND - exercise

Select all the information from the “salaries” table regarding contracts from 66,000 to 70,000 dollars per year.

Retrieve a list with all individuals whose employee number is not between ‘10004’ and ‘10012’.

Select the names of all departments with numbers between ‘d003’ and ‘d006’.

 

BETWEEN - AND- solution

SELECT

    *

FROM

    salaries;

 

SELECT

    *

FROM

    salaries

WHERE

    salary BETWEEN 66000 AND 70000

    ;

   

SELECT

    *

FROM

    employees

WHERE

    emp_no NOT BETWEEN '10004' AND '10012'

    ;

   

SELECT

    dept_name

FROM

    departments

WHERE

    dept_no BETWEEN 'd003' AND 'd006';

 

                                         IS NOT NULL / IS NULL



IS NOT NULL - IS NULL - exercise

Select the names of all departments whose department number value is not null.

 

IS NOT NULL - IS NULL - solution

SELECT

    dept_name

FROM

    departments

WHERE

    dept_no IS NOT NULL;

 

                                Other comparison operators 

 

Other comparison operators - exercise

Retrieve a list with data about all female employees who were hired in the year 2000 or after.

Hint: If you solve the task correctly, SQL should return 7 rows.

Extract a list with all employees’ salaries higher than $150,000 per annum. 

 

Other comparison operators - solution

SELECT

    *

FROM

    employees

WHERE

    hire_date >= '2000-01-01'

        AND gender = 'F';

SELECT

    *

FROM

    salaries

WHERE

    salary > 150000;

 

 

                                            SELECT DISTINCT 




SELECT DISTINCT - exercise

Obtain a list with all different “hire dates” from the “employees” table.

Expand this list and click on “Limit to 1000 rows”. This way you will set the limit of output rows displayed back to the default of 1000.

In the next lecture, we will show you how to manipulate the limit rows count.
 
 
SELECT DISTINCT - solution

SELECT DISTINCT

    hire_date

FROM

    employees;

 

                    Introduction to aggregate functions 

They are applied on multiple rows of the single column of a table and return an output of a single value.

 


 They ignore NULL values unless told not to.

 COUNT : 1:) select count(emp_no) from employee;

                 2:) select count(distinct first_name) from employee; 

 

 

Introduction to aggregate functions - exercise

How many annual contracts with a value higher than or equal to $100,000 have been registered in the salaries table?

How many managers do we have in the “employees” database? Use the star symbol (*) in your code to solve this exercise.

 

Introduction to aggregate functions - solution

SELECT

    COUNT(*)

FROM

    salaries

WHERE

    salary >= 100000;

SELECT

    COUNT(*)

FROM

    dept_manager;

 

 

                                                          ORDER BY

We can order output by using order by clause.To set the order by according to multiple columns we have to place the column names side by side separated with comma.

 

ORDER BY - exercise

Select all data from the “employees” table, ordering it by “hire date” in descending order.

 

ORDER BY - solution

SELECT

    *

FROM

    employees

ORDER BY hire_date DESC;

 

                                            GROUP BY 



By grouping the query - 1:) Distinct value will be selected.

                                           2:) In most cases , when you need an aggregate function , you must add a Group by clause  in your query too.

                                           3:) It is same same as the select distinct but , Group by is slower then the select distinct.

assume we need to column layout with name and the number of time that name occurs then : 

 



 
         4:) Always include the field you have grouped your results by in the SELECT STATEMENT.

 

practice :

    #group by clause

SELECT
    first_name
FROM
    employees;
   
#group by

SELECT
    first_name
FROM
    employees
GROUP BY first_name;

#counting with group by

SELECT
    COUNT(first_name)
FROM
    employees
GROUP BY first_name;

#getting the name and their total occurances

SELECT
    first_name, COUNT(first_name)
FROM
    employees
GROUP BY first_name;


#placing the order by clause too

SELECT
    first_name, COUNT(first_name)
FROM
    employees
GROUP BY first_name
ORDER BY first_name DESC;

 

         

                                                Using Aliases (AS)

                             Used to rename a selection from your query

 

Using Aliases (AS) - exercise

This will be a slightly more sophisticated task.

Write a query that obtains two columns. The first column must contain annual salaries higher than 80,000 dollars. The second column, renamed to “emps_with_same_salary”, must show the number of employees contracted to that salary. Lastly, sort the output by the first column. 

 

Using Aliases (AS) - solution

SELECT

    salary, COUNT(emp_no) AS emps_with_same_salary

FROM

    salaries

WHERE

    salary > 80000

GROUP BY salary

ORDER BY salary;

 

                                                       HAVING 


HAVING - exercise

Select all employees whose average salary is higher than $120,000 per annum.

Hint: You should obtain 101 records.

Compare the output you obtained with the output of the following two queries:

SELECT

    *, AVG(salary)

FROM

    salaries

WHERE

    salary > 120000

GROUP BY emp_no

ORDER BY emp_no;

 

SELECT

    *, AVG(salary)

FROM

    salaries

GROUP BY emp_no

HAVING AVG(salary) > 120000;

 

HAVING - solution

SELECT

    emp_no, AVG(salary)

FROM

    salaries

GROUP BY emp_no

HAVING AVG(salary) > 120000

ORDER BY emp_no;

When using WHERE instead of HAVING, the output is larger because in the output we include individual contracts higher than $120,000 per year. The output does not contain average salary values.

Finally, using the star symbol instead of “emp_no” extracts a list that contains all columns from the “salaries” table.

 

 

                                    WHERE vs HAVING- Part I

 


 

 

                                        WHERE vs HAVING- Part II


 

WHERE vs HAVING - Part II - exercise

Select the employee numbers of all individuals who have signed more than 1 contract after the 1st of January 2000.

Hint: To solve this exercise, use the “dept_emp” table.

 

WHERE vs HAVING - Part II - solution

SELECT

    emp_no

FROM

    dept_emp

WHERE

    from_date > '2000-01-01'

GROUP BY emp_no

HAVING COUNT(from_date) > 1

ORDER BY emp_no;

 

                                                    LIMIT



 


LIMIT - exercise

Select the first 100 rows from the ‘dept_emp’ table. 

 

LIMIT - solution

SELECT

    *

FROM

    dept_emp

LIMIT 100;

 

 

 

Comments

Post a Comment

Popular posts from this blog

preparation analytics

                                                            Preparation Test      date : 28/09/2020    CSS :      1: Explain the CSS “box model” and the layout components that it consists of.             Provide some usage examples.      The CSS box model is a rectangular layout paradigm for HTML elements that consists of the following: Content - The content of the box, where text and images appear Padding - A transparent area surrounding the content (i.e., the amount of space between the border and the content) Border - A border surrounding the padding (if any) and content Margin - A transparent area surrounding the border (i.e., the amount of space between the border and any neighboring elements) Each of these properties can be specified independently for each side of the element (i.e., top, right, bottom, left) or fewer values can be specified to apply to multiple sides. For example: /* top right bottom left */ padding : 25 px 50 px 75 px 100 px ; /
Read more

Higher package

  https://www.quora.com/Which-job-gives-2-to-3-lakhs-per-month-for-freshers   There are so many IT/ Software companies that pay more than Rs. One Lakh per month. Getting one lakh means an annual package of about Rs. 15 Lacs. Almost all of my friends in software are getting more than this, ranging from 15L to 30L. Typically any company or a startup that has raised a funding of USD 5 million (35 Crores) is able to hire software developers, testers for more than 15 L of package. Following are the examples of a few such companies - Uber, Ola, Flipkart, Amazon, PayTM, Mobiqwik, Oyo Rooms, MakeMyTrip, Clear Trip, Ibibo, Microsoft, Facebook, Google, Grofers, Big Basket, Rivigo, Healthify, PayU, RazorPay, Zapr, Swiggy, Zomato, Hike, Tapzo, Deutsche Bank, Oracle, etc. and the list goes on. Examples from companies paying at non IT roles, Godrej, Western Union, McKinsey & Company, BCG, TCS, Ola, Flipkart, Amazon, PayTM, etc. There are many more. Since I'm from Software b
Read more

General info - 6350958828e0b

Image
 Add new column of id : ALTER TABLE spam_alter_email  ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ; exporting large db with ternimal : mysqldump --max_allowed_packet=1024M -u root -p database > dumpfile.sql   /home/rajeshwar/Videos/Cluster_query/2023-10-06/Cluster_New_update     Gravtar logo : https://ui-avatars.com/api/?name=John+Doe&background=random  load data from file into database : LOAD DATA INFILE '/home/export_file.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '/n' IGNORE 1 ROWS; download large set of databse : mysqldump -h 127.0.0.1 -u root -p spam_alter > database-dump-alter.sql Note for all this : 1: we have to modify max_upload_file_size , memory_size in the php.ini file. 2:php.ini file lives under the composer folder and then php version folder.   plocation key : 2c0bc0ca84f049c9ac43848eb0189e30 URL : $ip = '8.8.8.8' 'https://api.ipgeolocation.io/ipgeo?apiKey=2c
Read more