Articles and cookbooks with the tag

Standard SQL

12 Nov 2023

How to Select NULL Values in SQL

Problem: You want to select rows with the NULL value in a given column. Example: Let's see a table named Employees with the following columns: EmployeeID, EmployeeName, Dept, and Salary. Assume the Salary column allows NULL values. EmployeeIDEmployeeNameDeptSalary 1Jack RusselManager5600 2Jan KowalskiHRnull 3John DoeHR4500 4Mark RusselSales9000 5Jane DoeSalesnull Now, let’s retrieve all records where the Salary column contains NULL values.

12 Nov 2023

How to Insert Multiple Rows in SQL

Problem You want to insert multiple rows into an SQL table using one query instead of one insert per query. Example You have a table called Customers with columns CustomerID, Name, Email, and Address. Let’s look at the table: CustomerIDNameEmailAddress 1Jack Russeljrussel@email.com123 Abbey Road 2Jan Kowalskijkowalski@email.com456 Main Ave 3Mark Russelmussel@email.com789 Main St 4Marta Wilsonmwilson@email.com123 Red St You have a list of new customers you want to add to the database table.

12 Nov 2023

How to Compare Datetime Values in SQL

Problem You want to compare two datetime values in an SQL WHERE clause. Example 1 The database for an online store has a table named orders with data in the columns order_id and order_time. order_idorder_timepayment_time 2984632021-12-31 08:36:542022-01-01 15:06:41 4038202022-03-10 21:10:012022-03-10 22:07:34 9688322022-07-15 12:59:132022-07-15 12:59:13 We want to find information for each order placed after 2021, i.

12 Nov 2023

How to Compare Date Values in SQL

Problem You want to compare two date values in SQL. Example 1 The database for an online store has a table named orders with data in the columns order_id and order_date. order_idorder_datepayment_date 2984632021-12-312022-01-01 4038202022-03-102022-03-10 9688322022-07-152022-07-15 We want to find information for each order placed after 2021, i.e., since the start of 2022. Solution 1 Filter down to the users who placed an order after 2021 by order_time in the WHERE clause.

1 May 2023

How to Use Multiple WHERE Conditions in a Single Query

Problem: You want to apply multiple conditions to filter the data you want to retrieve. Example 1: A company has its employee data stored in the table employees with the columns empId, empName, dept, and salary. empIdempNamedeptsalary 1Anthony VillaSales3400.00 2Megan WhitneyHR4000.00 3Clayton CruzFinance3500.00 4Ahmed LiFinance4150.00 5Anna NewtonManager6450.00 6Filip SchaeferHR2850.00 Let’s say you want to find all who work in the Finance department and are paid more than 4,000.

30 Jan 2023

How to Compare 2 Dates in the WHERE Clause in SQL

Problem You want to compare values of two dates in an SQL WHERE clause. Example 1 A social platform’s database has a table named users with data in the columns user_id, nickname, and registration_date. user_idnicknameregistration_date 5637542878superuser2022-02-23 2356782364helloworld2021-05-09 7325629347toolazytothink2021-12-12 We want to find information for each user who was registered before 2022, i.e. until the end of 2021.

30 Jan 2023

How to Check ‘Is Not Equal’ in SQL

Problem You want to check if two values are not equal in SQL. Example A social platform’s database has a table named employees with data in the columns first_name, last_name, and hire_date. first_namelast_namehire_date OliverCrawford2021-03-15 WilliamParker2022-01-22 RalphCrawford2009-10-01 LaneWalters2020-03-11 KeithPope2021-07-08 JeffreyRead2021-09-25 We want to select all employees except those with the last name of Crawford – i.

24 Oct 2022

How to Subtract one Value From Another in SQL

Problem: You want to subtract one numeric value from another in SQL. Example: As an example, let’s take the table revenue. It stores information on income and expenses for different months. The table has 4 columns: year, month, income, and expenses. yearmonthincomeexpenses 2022June86004300 2022July55004500 2022August90007300 2022September120008000 Let’s calculate the profit for each month.

24 Oct 2022

How to Insert a Single Quote in SQL

Problem You want to insert a record that contains a single quote symbol, e.g., "O’Hara", into an SQL table. Example Our database has a table named employees with data in the following columns: id (primary key), first_name, last_name, and department. idfirst_namelast_namedepartment 1MichaelTaylorHR 2MilesReeseSales 3KarlRuralSales 4OliviaScottManager Let’s say we want to insert a new employee in the sales department: William O’Hara.

24 Oct 2022

How to Compare two Strings in SQL

Problem You want to to compare two strings alphabetically in SQL. Solution The most straightforward method to compare two strings in SQL is to use standard comparison operators (<, >, =, etc.): SELECT 'Michael' < 'Mike'; Here is the result: 'Michael' < 'Mike' 1 The result of 1 means 'true'. A result of 'false' would show a 0.

11 Sep 2022

How to Get the Previous Month in SQL

Problem You would like to display the previous month (without time information) in a database. Solution For PostgreSQL and MySQL: SELECT EXTRACT(MONTH FROM CURRENT_TIMESTAMP - INTERVAL '1' MONTH) AS previous_month; For Oracle: SELECT EXTRACT(MONTH FROM CURRENT_TIMESTAMP - INTERVAL '1' MONTH) AS previous_month FROM dual; Assuming today is March 4, 2020, the result is: previous_month 2 Discussion To get the previous month, subtract one month from today's date.

11 Sep 2022

How to Escape Single Quotes in SQL

Problem You need to escape a single quote in SQL – i.e. include a name with an apostrophe (e.g. Lay’s) in a string. Example You are trying to insert some text data into your table in SQL, like so: INSERT INTO customer (id, customer_name) VALUES (501, 'Chipita'); As you are trying to insert a text string (the customer name) you must indicate the start and end of the name with single quotes.

22 May 2022

How to Check the Length of a String in SQL

Problem You want to know the number of characters in a string. Example Our database has a table named employees with data in the following columns: id (primary key), first_name, last_name, and city. idfirst_namelast_namecity 1OwenTaylorManchester 2HaroldWilkinsonManchester 3KarlGaversDallas 4MaryLarsonDallas Let’s say that we want to select the ID, last name, and city of each employee.

1 Feb 2022

How to Write a CASE Statement in SQL

Problem: You want to use a CASE statement in SQL. Example: You have exam results in the exam table. You need to assign each result to one of the following text values: 'bad result', 'average result', or 'good result'. Bad results are those below 40, good results are those above 70, and the rest are average results. The exam table looks like this: nameresult Toby Shaw56 Casey Watson49 Bennie Lynn23 Lane Sloan70 Steff Fox85 Reggie Ward40 Gail Kennedy66 Brice Mueller90 Solution 1: SELECT name, result, CASE WHEN result 70 THEN 'good result' ELSE 'average result' END AS category FROM exam; The result table looks like this:

30 Jan 2022

How to Drop a Table in SQL

Problem You want to remove a table from a database. Example We would like to remove the table called product from the database Solution DROP TABLE product; Discussion If you want to remove an existing table from a database, use the statement DROP TABLE with the name of the table. In this example, we first wrote the command DROP TABLE followed by the table name product. That’s all you need to do.

30 Jan 2022

How to Delete a Column in SQL

Problem You want to remove a column from a table. Example We would like to delete the column description from the table product. Solution ALTER TABLE product DROP COLUMN description; Discussion SQL provides the ALTER TABLE statement to change the structure of a table. If you’d like to remove a column from a table, use this statement. First, write ALTER TABLE, followed by the name of the table you want to change (in our example, product).

24 Oct 2021

How to Create View in SQL

Problem You want to create a view from a table in a database. Example We would like to create a view called it_employee with employees who work in the IT department, based on data from the table employee. Solution CREATE VIEW it_employee AS SELECT first_name, last_name FROM employee WHERE department = 'IT'; Discussion If you want to create a new view in a database, use the CREATE VIEW keyword followed by the name of the view (in our example: it_employee).

24 Oct 2021

How to Change Text to Lowercase in SQL

Problem You’d like to change some text to lowercase in SQL. Example Our database has a table named item with data in the following columns: id, code, and wood_type_used. The case is inconsistent in the data in the wood_type_used column. We would like all of the wood types to be displayed in lowercase. idcodewood_type_used 1000237PSHPine 2000115MCHMAHOGANY 3000073BTBbirch 400068WBDwAlnUt 500055BSHBirch Solution We’ll use the LOWER() function:

24 Oct 2021

How to Add a Column in SQL

Problem You want to add a new column to an existing table. Example We would like to add the column color of the datatype varchar to the table called jeans. Solution ALTER TABLE jeans ADD color varchar(100) NOT NULL; Discussion SQL provides the statement ALTER TABLE that allows you to change the structure of a table. It can be used to modify the table by adding a new column.

19 Oct 2021

How to Sort in SQL

Problem: You would like to sort the result of an SQL query in ascending or descending order. Example: Our database has a table named salary_information with data in the columns id, first_name, last_name, and monthly_earnings. We want to sort the employees by their monthly_earnings in descending order. idfirst_namelast_namemonthly_earnings 1CalvinRios3500 2AlanPaterson4000 3KurtEvans2300 4AlexWatkins5500 Solution: We will use an ORDER BY clause.

18 Oct 2021

How to Get all Row Combinations From Two Tables in SQL

Problem You want to get all combinations of rows from two tables in SQL. Example Here are two tables: one contains letters (letters), and the other contains numbers (numbers): letter X Y number 0 1 2 Solution 1 In order to combine every row of the letters table with every row of the numbers table, we will use the CROSS JOIN:

29 Aug 2021

How to not Show Duplicates in SQL

Problem: You’d like to display non-duplicate records in SQL. Example: Our database has a table named City with data in the columns id, name, and country. idnamecountry 1MadridSpain 2BarcelonaSpain 3WarsawPoland 4CracowPoland Let’s get the names of the countries without duplicates. Solution: We’ll use the keyword DISTINCT. Here’s the query: SELECT DISTINCT country FROM City; Here’s the result of the query:

29 Aug 2021

How to Use DISTINCT in SQL

Problem: You’d like to query your data to return the result without duplicate rows. Imagine you have a table with repeated entries, and you only want each unique entry to show up once. This helps in getting a cleaner and more concise set of results, making analysis easier. Example: Our database has a table named books with data in the columns author_firstname, author_lastname, and book_title. You’d like to get a list of unique first and last names of the authors.

22 Jul 2021

How to Delete a Row in SQL

Problem You want to remove a row / rows from a table. Example 1 In the exam table, there are names of the students and the results of the exam. nameresult Janet Morgen9 Taya Bain11 Anne Johnson11 Josh Kaur10 Ellen Thornton8 You want to remove the row for Ellen Thornton.

22 Jul 2021

How to Comment in SQL

Problem You'd like to comment your code in SQL. Example Here's the example code: SELECT name, COUNT(*) AS count_items FROM products GROUP BY name HAVING COUNT(*) 5 ORDER BY name DESC; Solution 1: Use -- to comment till the end of the line Use -- to comment code till the end of the line. Here is what it looks like: -- product names along with the number of items -- products ordered by name in descending order SELECT name, COUNT(*) -- AS count_items FROM products GROUP BY name -- HAVING COUNT(*) 5 ORDER BY name DESC; Discussion You can write single-line comments in SQL using --.

24 Jan 2021

How to Group by Two Columns in SQL

Problem You want to group your data by two columns so you can count some statistics. Example In the order table, you have the columns order_date, product_id, customer_id, and number. You would like to count the number of products bought by each customer each day. The order table looks like this: order_dateproduct_idcustomer_idnumber 2023-11-25711 2023-11-251213 2023-11-265312 2023-11-26124 2023-11-26321 2023-11-261627 2023-11-26332 2023-11-27631 Solution SELECT order_date, customer_id, SUM(number) AS products_number FROM order GROUP BY order_date, customer_id; The result is:

24 Jan 2021

How to Delete a Foreign Key Constraint in SQL

Problem You want to delete a foreign key from a table in a database. Example We want to remove the foreign key named fk_student_city_id from the table student. Solution ALTER TABLE student DROP CONSTRAINT fk_student_city_id; Discussion To delete a foreign key from a table, use the ALTER TABLE clause with the name of the table (in our example, student) followed by the clause DROP CONSTRAINT with the name of the foreign key constraint.

24 Jan 2021

How to Create a Table with a Foreign Key in SQL

Problem You want to create a foreign key for a table in a database. Example We would like to create a table named student that contains a foreign key that refers to the id column in the table city. Solution 1: Creating new table with single-column foreign key CREATE TABLE student ( id INT PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, city_id INT FOREIGN KEY REFERENCES city(id) ); Discussion To create a new table containing a foreign key column that references another table, use the keyword FOREIGN KEY REFERENCES at the end of the definition of that column.

30 Dec 2020

How to remove a unique constraint in SQL?

Problem: You would like to remove a unique constraint from the column/columns in a table in a database. Example: We would like to drop the unique constraint in the table product from the column name. The statement below shows how to do it. Solution ALTER TABLE product DROP CONSTRAINT UQ_product_name In this example the table product is altered by using the ALTER TABLE clause. After this clause you enter the name of the table (in our example: product) and the clause DROP CONSTRAINT with the name of the unique constraint you want to remove.

30 Dec 2020

How to Make a Column Unique in SQL?

Problem: You would like to make a column unique in a given table in a database. Example: We would like to make the column name unique in the table product. The query below presents one way to do it. Solution 1: Creating new table with a UNIQUE constraint CREATE TABLE product ( id INT NOT NULL PRIMARY KEY, name VARCHAR(100) UNIQUE, producer VARCHAR(100), category VARCHAR(100) ); Discussion: In this example a given column (the column name) was made unique by adding the clause UNIQUE at the end of the column definition (name VARCHAR(100) UNIQUE).

30 Dec 2020

How to Calculate a Square Root in SQL

Problem You want to find the square root of a number in SQ:. Example You want to compute the square root of all numbers in the column number from the table data. number 9 2 1 0.25 0 -4 Solution SELECT number, SQRT(number) AS square_root FROM data; The result is:

6 Nov 2020

How to Remove a Primary Key in SQL

Problem: You want to drop a primary key from a table in a database. Example: We want to remove the primary key from the table product. Solution 1: ALTER TABLE product DROP PRIMARY KEY; Discussion: To drop a primary key from a table, use an ALTER TABLE clause with the name of the table (in our example, product) followed by the clause DROP PRIMARY KEY. Since a table can have only one primary key, you don’t need to specify the primary key column(s).

6 Nov 2020

How to Create a Primary Key in SQL

Problem You want to create a primary key for a table in a database. Example We want a primary key for a table called product. Solution 1: Creating new table with a single-column primary key CREATE TABLE product ( id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, producer VARCHAR(100) NOT NULL, price DECIMAL(7,2) ); Discussion To create a new table with a column defined as the primary key, you can use the keyword PRIMARY KEY at the end of the definition of that column.

6 Nov 2020

How to Compute an Absolute Value in SQL

Problem You want to find the absolute value of a number in SQL. Example You want to compute the absolute value of each number in the column numbers from the table data. numbers -3.2 0 20 Solution SELECT ABS(numbers) AS absolute_values FROM data; The result is: absolute_values 3.

6 Nov 2020

How to Calculate a Square in SQL

Problem You want to find the square of a number in SQL. Example You want to compute the square of each number in the column number from the table data. number 3 1 0.5 0 -2 Solution 1: Use SQUARE function SELECT number, SQUARE(number) AS square FROM data; Solution 2: Use multiplication operator * SELECT number, number * number AS square FROM data; Solution 3: Use POWER function SELECT number, POWER(number, 2) AS square FROM data; The result is:

10 Oct 2020

How to Create a Table from an SQL Query

Problem You would like to create a new table in a database with data defined by an SQL query. Example We would like to create the table gamer based on an SQL query. In this query, we select data from another table named championship presented below. idgamerscorechampionship_date 1alice142020-08-10 2supervisor102020-09-28 3happyman02020-08-10 4lukas62020-08-10 5oli122020-08-10 6biggamer72020-09-12 In the database, let’s create a new table named gamer which will store data in all of the columns defined in the table championship (id, gamer, score, and championship_date).

10 Oct 2020

How to Create One Table From Another Table in SQL

Problem You would like to create a new table with data copied from another table. Example Our database has a table named product with data in the following columns: id (primary key), name, category, and price. idnamecategoryprice 105roseflower5.70 108deskfurniture120.00 115tulipflower6.50 123sunflowerflower7.50 145guitarmusic300.00 155orchidflower9.50 158flutemusic156.00 In the database, let’s create a new table named florist which will store the following columns: id, name, and price.

9 Oct 2020

How to Select the First Row in Each GROUP BY Group

Problem: You've grouped your data with GROUP BY and would like to display only the first row from each group. Example: Our database has a table named exam_results with data in the following table: first_namelast_nameyearresult JohnKlein202040 EdithBlack202043 MarkJohnson201932 LauraSummer202035 KateSmith201941 JacobBlack201944 TomBennett202038 EmilyKelly202043 For each year, let's find the student with the best result.

27 Apr 2020

How to Order Rows by Group Sum in SQL

Problem: You’d like to order rows by the sums generated by a group of records. Example: Our database has a table named training with data in four columns: id, login, year, and score. idloginyearscore 1Andy201824 2Lucy201925 3Andy201920 4Lucy201816 5Gary201918 6Gary201819 7Gary201722 8Lucy201721 9Andy201726 Let’s get the login name of each player along with the total sum of score across all years, putting records in descending order according to players’ total scores.

27 Apr 2020

How to Combine the Results of Two Queries in SQL

Problem You’d like to display data from given columns (of a similar data type) from two tables in SQL. Example There are two tables in our database: employee and customer. The employee table contains data in the following columns: id, first_name, last_name, and age. idfirst_namelast_nameage 1TomMiller22 2JohnSmith26 3LisaWilliams30 4CharlesDavis21 5JamesMoore22 The customer table contains data in the following columns: id, first_name, last_name, and age.

21 Mar 2020

How to Rank Rows Within a Partition in SQL

Problem: In the result set, you’d like to partition records and rank rows within each partition, adding another column to show rows’ ranks within the partition. Example: Our database has a table named magazine with data in the following columns: id (primary key), name, category, and price. idnamecategoryprice 105Country Livinglifestyle1.70 108News Magazinenews3.35 115Decibelmusic6.50 123Drum Magazinemusic6.50 145Sunsetlifestyle12.

15 Feb 2020

How to Find Rows with Minimum Value

Problem You want to find rows which store the smallest numeric value in a column. Example Our database has a table named weather with data in the following columns: id, city, and temperature. You want to find cities with the lowest temperature. idcitytemperature 1Houston23 2Atlanta20 3Boston15 4Cleveland15 5Dallas34 6Austin28 Solution SELECT id, city, temperature FROM weather WHERE temperature = (SELECT MIN(temperature) FROM weather); Here’s the result:

15 Feb 2020

How to Find Rows with Maximum Value

Problem You want to find rows which store the largest numeric value in a given column. Example Our database has a table named student with data in the following columns: id, first_name, last_name, and grade. You want to find the students who have the highest grades. idfirst_namelast_namegrade 1LisaJackson3 2GaryLarry5 3TomMichelin2 4MartinBarker2 5EllieBlack5 6MarySimpson4 Solution SELECT id, first_name, last_name, grade FROM student WHERE grade = (SELECT MAX(grade) FROM student); Here’s the result:

15 Feb 2020

How to Find Records with NULL in a Column

Problem You want to find records with NULL in a column. Example Our database has a table named children with data in four columns: id, first_name, middle_name, and last_name. idfirst_namemiddle_namelast_name 1JohnCarlJackson 2TomNULLThomson 3LisaAliceNULL 4AnneNULLSmith Let’s find the id, first name, and last name of children without a middle name (NULL in column middle_name). We use the IS NULL operator for this. Solution SELECT id, first_name, last_name FROM children WHERE middle_name IS NULL; Here’s the result:

15 Feb 2020

How to Filter Rows without NULL in a column

Problem You want to find records without a NULL in a column. Example Our database has a table named product with data in three columns: id, name, and price. idnameprice 1butterNULL 2milk2.35 3bread3.25 4cheeseNULL Let’s find the names and prices of products that have a price (without a NULL). Do this by using the IS NOT NULL operator.

15 Feb 2020

How to Filter Records with Aggregate Function SUM

Problem You need to filter groups of rows by the sum of a given column. Example Our database has a table named company with data in the following columns: id, department, first_name, last_name, and salary. iddepartmentfirst_namelast_namesalary 1marketingLoraBrown2300 2financeJohnJackson3200 3marketingMichaelThomson1270 4productionTonyMiller6500 5productionSallyGreen2500 6financeOlivierBlack3450 7productionJenifferMichelin2800 8marketingJeremyLorson3600 9marketingLouisSmith4200 Let’s find the names of departments that have sums of salaries of its employees less than 7000.

15 Feb 2020

How to Filter Records with Aggregate Function COUNT

Problem You want to find groups of rows with a specific number of entries in a group. Example Our database has a table named product with data in the following columns: id, name and category. idnamecategory 1sofafurniture 2glovesclothing 3T-Shirtclothing 4chairfurniture 5deskfurniture 6watchelectronics 7armchairfurniture 8skirtclothing 9radio receiverelectronics Let’s find the category of products with more than two entries.

15 Feb 2020

How to Filter Records with Aggregate Function AVG

Problem You want to filter groups of rows in by the average value of a given column. Example Our database has a table named product with data in the following columns: id, name, store and price. idnamestoreprice 1milkGreen Shop2.34 2breadClark’s Grocery3.56 3breadSuper Market4.15 4milkSuper Market1.80 5breadGrocery Amanda2.26 6milkViolet Grocery3.45 7milkClark’s Grocery2.10 8breadViolet Grocery2.

15 Feb 2020

How to Concatenate Strings in SQL

Problem You want to concatenate strings from two columns of a table into one. Example Our database has a table named student with data in the following columns: id, first_name and last_name. idfirst_namelast_name 1LoraSmith 2EmilBrown 3AlexJackson 4MartinDavis Let’s append the first name to the last name of the student in one string. Use a space between each name.

9 Feb 2020

What’s the Difference Between RANK and DENSE_RANK in SQL?

Problem You want to compare the rankings produced by RANK and DENSE_RANK functions. Example Our database has a table named sales_assistant with data in the following columns: id, first_name, last_name, month, and sold_products. We want to display each sales assistant’s first and last name and the number of sold products. We also want to rank them in terms of the number of products sold in descending order. idfirst_namelast_namemonthsold_products 1LisaBlack 52300 2MaryJacobs52400 3LisaBlack 62700 4MaryJacobs62700 5AlexSmith 62900 6MaryJacobs71200 7LisaBlack 71200 8AlexSmith 71000 Solution 1: Using RANK and DENSE_RANK SELECT RANK() OVER(ORDER BY sold products DESC) AS rank, DENSE_RANK() OVER(ORDER BY sold products DESC) AS dense_rank, first_name, last_name, month, sold_products FROM sales_assistant; This query returns two rankings: one produced by RANK and another by DENSE_RANK.

9 Feb 2020

How to Number Rows in SQL

Problem: You would like to give each row in the result table a separate number. Example: Our database has a table named furniture with data in the following columns: code and name. The furniture table stores the name of pieces of furniture that we want to number. codename 101bed 202sofa 333chair 123bookcase 235table 766desk Solution: SELECT ROW_NUMBER() OVER() AS num_row, name, code FROM furniture; The query returns the row number of each piece of furniture along with its name and code:

9 Feb 2020

How to Find the Minimum Value of a Column in SQL

Problem You’d like to find the smallest numeric value in a column. Example Our database has a table named employment with data in the following columns: id, first_name, last_name, department, and salary. idfirst_namelast_namedepartmentsalary 1EllieMartinesmarketing1200 2MartinJohnsonfinance2300 3MichaelJacobsproduction1100 4StephenKowalskimarketing4300 5StanleyMillermarketing3500 6JenyBrownfinance5000 7MargaretGreenmarketing1500 8LisaThomasproduction2800 Let’s find the lowest salary among all employees.

9 Feb 2020

How to Find the Maximum Value of a Numeric Column in SQL

Problem You’d like to find the maximum value of a numeric column. Example Our database has a table named product with data in the following columns: id, name, year, and items. idnameyearitems 1bread roll2018345 2chocolate2017123 3butter201934 4bread roll2019456 5butter201856 6butter201778 7chocolate201987 8chocolate201876 Let’s find the maximum number of items sold over all years.

9 Feb 2020

How to Find the Average of a Numeric Column in SQL

Problem You’d like to calculate the average of numbers in a given column. Example Our database has a table named sale with the following columns: id, city, year, and amount. We want to calculate the average sales, regardless of city or year. idcityyearamount 1Los Angeles20172345.50 2Chicago20181345.46 3Annandale2016900.56 4Annandale201723230.22 5Los Angeles201812456.20 6Chicago201789000.40 7Annandale201821005.

9 Feb 2020

How to Convert a String to Uppercase in SQL

Problem You would like to convert a string to uppercase in SQL. Example Our database has a table named questionnaire with data in the following columns: id, first_name, last_name, and favorite_car. idfirst_namelast_namefavorite_car 1AlanJacksonHonda Civic 2ElisaThomsonTOYOTA Camry 3MaryMartinesNissan rogue 4ChrisBrownford focus 5AdamSpringRam PICKUP Our table stores the make and model of the favorite car for each person who filled out our questionnaire.

27 Nov 2019

How to Use LIKE in SQL

Problem: You need to search for specific patterns (certain combinations of letters or numbers) within text data in your database. We'll show you how to use the LIKE operator in SQL to: Find city names starting with S Find city names with exactly five characters Find city names starting with S and with o as the second-to-last character You'll also learn how to use LIKE and wildcard operators in SQL to find your own patterns in text data in your database.

27 Nov 2019

How to Round Up a Number to the Nearest Integer in SQL

Problem: You want to round up a number to the nearest integer in SQL. Example: Our database has a table named rent with data in the following columns: id, city, area, and bikes_for_rent. idcityareabikes_for_rent 1Los Angeles1302.151000 2Phoenix1340.69500 3Fargo126.44101 Let’s show each city’s name along with the ratio of its area to the number of bikes for rent.

27 Nov 2019

How to Round Numbers in SQL

Problem: You want to round a number to a specific number of decimal places in SQL. Example: Our database has a table named product with data in the following columns: id, name, and price_net. idnameprice_net 1bread2.34 2croissant1.22 3roll0.38 Suppose there’s a tax of 24% on each product, and you’d like to compute the gross price of each item (i.e., after taxes) and round the value to two decimal places.

27 Nov 2019

How to Floor Numbers in SQL

Problem You want to round a number down to the nearest integer. Example Our database has a table named employee with data in the following columns: id, first_name, last_name, and hours_worked (for the current month). idfirst_namelast_namehours_worked 1AlanWatson95 2LisaBlack225 3LauraWilliams104 Let’s show the first and last name of each employee along with the number of days they worked.

27 Nov 2019

How to Add Rankings with DENSE_RANK() in SQL

Problem You want to add a ranking position to rows consecutively, even if the rows have the same values. Example Our database has a table named competition with data in the following columns: id (primary key), first_name, last_name, and score. idfirst_namelast_namescore 11JohnThomas345 14MaryJohnson222 16LisaBrown154 23AlanBlake222 32ChrisTaylor154 Let’s display all details about students: their last name, first name, and scores sorted by score in descending order.

26 Nov 2019

How to Trim Strings in SQL

Problem: You’d like to remove specific characters from the beginning and end of a string in SQL. Example: Our database has a table named post with data in two columns: id and title. idtitle 1' News about dogs ' 3'Eurovision 2019 ' 4'New political climate' Let’s trim the title of each post to remove the unnecessary space at the beginning and end.

26 Nov 2019

How to Sum Values of a Column in SQL?

Problem: You’d like to compute the sum the values of a column. Example 1: Computing the Total Sum for a Column Our database has a table named game with the following columns: id, player, and score. You want to find the total score obtained by all players. idplayerscore 1John134 2Tom 146 3Lucy20 4Tom 118 5Tom 102 6Lucy90 7Lucy34 8John122 Solution: SELECT SUM(score) as sum_score FROM game; Here’s the result:

26 Nov 2019

How to Replace Part of a String in SQL

Problem: You’d like to replace part of a string with another string. Example: Our database has a table named investor with data in the following columns: id, company, and phone. idcompanyphone 1Big Market123–300-400 3The Sunny Restaurant123–222-456 4My Bank123-345-400 We’d like to change the phone number format for each company by replacing the hyphen character with a space.

26 Nov 2019

How to Count the Number of Rows in a Table in SQL

Problem You’d like to determine how many rows a table has. Example Our database has a table named pet with data in the following columns: id, eID (electronic identifier), and name. ideIDname 123456sparky 223457mily 3NULLlessy 4NULLcarl 534545maggy Let’s count all rows in the table. Solution COUNT(*) counts the total number of rows in the table:

25 Nov 2019

How to Order by Count in SQL?

Problem: You aggregated data into groups, but you want to sort the records in descending order by the number of elements in the groups. Example: Our database has a table named user with data in the following columns: id, first_name, last_name, and country. idfirst_namelast_namecountry 1LisaWilliamsEngland 2GaryAndersPoland 3TomWilliamsPoland 4MichaelBrownFrance 5SusanSmithUSA 6AnneJonesUSA 7EllieMillerPoland Let’s create a report on our users.

25 Nov 2019

How to Order By Two Columns in SQL?

Problem: You need to display records from a given table sorted by two columns. Example: Our database has a table named employee with the following columns: id, first_name, last_name, and salary. idfirst_namelast_namesalary 1LisaUlman3000 2AdaMuller2400 3ThomasGreen2400 4MichaelMuller3000 5MaryGreen2400 Let’s display all information for each employee but sort the records according to salary in descending order first and then by last name in ascending order.

25 Nov 2019

How to Multiply Two Columns in SQL

Problem: You want to multiply values from two columns of a table. Example: Our database has a table named purchase with data in the following columns: id, name, price, quantity, and discount_id. idnamepricequantitydiscount_id 1pen731 2notebook582 3rubber1131 4pencil case2423 Let’s multiply the price by the quantity of the products to find out how much you paid for each item in your order.

25 Nov 2019

How to Join on Multiple Columns

Problem You want to join tables on multiple columns by using a primary compound key in one table and a foreign compound key in another. Example Our database has three tables named student, enrollment, and payment. The student table has data in the following columns: id (primary key), first_name, and last_name. idfirst_namelast_name 1EllieWillson 2TomBrown 3SandraMiller The enrollment table has data in the following columns: primary key (student_id and course_code), is_active, and start_date.

25 Nov 2019

How to Handle Divide by Zero In SQL

Problem You want to perform division in your SQL query, but the denominator is an expression that can be zero. The database will give you an error when the denominator is in fact zero. Example Our database has a table named investor_data with data in the following columns: id, investor_year, price_per_share, income, and expenses. idinvestor_yearprice_per_shareincomeexpenses 120162032002300 2201713020002000 3201840200100 420191559004900 Let’s divide the price per share by the difference between income and expenses to determine the P/E ratio (price-earnings ratio) for each year.

25 Nov 2019

How to Find Duplicate Rows in SQL?

Problem You have duplicate rows in your table, with only the IDs being unique. How do you find those duplicate entries? Example Our database has a table named product with data in the following columns: id, name, and category. idnamecategory 1steakmeat 2cakesweets 3steakmeat 4porkmeat 5cakesweets 6cakesweets Let’s find duplicate names and categories of products.

25 Nov 2019

How to Eliminate Duplicate Rows in SQL

Problem Duplicate rows in your SQL query results can make data harder to understand and reduce the accuracy of your analysis. When the same data appears more than once, it clutters your output and complicates reports. Solving this issue is important for maintaining clean, easy-to-read, and reliable datasets that lead to better insights and decisions. Example Our database has a table named clothes with data in the following columns: id, name, color, and year_produced.

25 Nov 2019

How to Count Distinct Values in SQL

Problem You’d like to count how many different non-NULL values there are in a given column. Example Our database has a table named customer with data in the following columns: id, first_name, last_name, and city. idfirst_namelast_namecity 1JohnWilliamsChicago 2TomBrownAustin 3LucyMillerChicago 4EllieSmithDallas 5BrianJonesAustin 6AllanDavisNULL Let’s find the number of different (and non-NULL) cities.

20 Nov 2019

How to Order Alphabetically in SQL

Problem You want to display records from a table in alphabetical order according to given column. Example Our database has a table named customer. The customer table contains data in the id, first_name, and last_name columns. We want to display customer’s information, sorted in ascending order by their last name. id first_name last_name 1 Susan Thomas 2 John Michael 3 Tom Muller Solution SELECT id, first_name, last_name, FROM customer ORDER BY last_name ASC; This query returns sorted alphabetically records: