What are some other useful things that SQL lets us do?

Question

In the context of this exercise, what are some other useful things that SQL lets us do?

Answer

SQL provides us with a lot of useful functionality for interacting with databases.

When retrieving data from a database, we can use SQL to select specific rows based on some condition. For example, this will retrieve just the rows that satisfy the condition following the WHERE clause, which is when the value under the col column is greater than 50.

SELECT col FROM table
WHERE col > 50;

SQL also lets us aggregate data, which means to summarize observations for groups of data. For example, SQL provides aggregate functions to get the sum or the average of all values of a column, returning a single value, or summary.

In addition, we can even combine multiple tables using the JOIN clause. We can combine tables by essentially stacking all the rows of one table on top of the other, or we can combine them based on some conditions or on matching values of some columns only.

These are only a few of the many useful features that SQL provides. Feel free to check out some SQL documentation or the Codecademy SQL courses for more useful things that SQL lets us do!

12 Likes

With this same logic I tried to select only females in the database but I couldn’t figure out how. I tried:

SELECT gender FROM table
WHERE gender += female;

and

SELECT gender FROM users
WHERE gender += female;

and

SELECT female FROM users;

No luck :frowning: How would i go about selecting specific users based on their info?

3 Likes

To select only females, you would write:

SELECT * FROM users
WHERE gender = ‘Female’;

This will first return all (*) columns from the ‘users’ table,
then it will only return rows where the gender column has a value of ‘Female’ (make sure to wrap Female in quotes (’’), as Female is of the string data type

23 Likes

How would i select only users with emails containing ‘facebook.com’ ?

2 Likes

You can use something like

WHERE “email” LIKE ‘%facebook.com

I am quite new to this, but the ‘%’ is what you want to use in this situation.

8 Likes

How do you do multiple data selection criteria, example I want to do this:

SELECT * FROM users
WHERE membership_id <= ‘2000’;
WHERE gender = ‘Male’;

1 Like

use AND or OR, depending if you want both or one of the conditions to be met

9 Likes

According to the official mysql documentation:

https://dev.mysql.com/doc/refman/8.0/en/where-optimization.html

you only need the where keyword once:

WHERE condition1 AND condition2

same for OR.

4 Likes

Yes, You are right I did a mistake in there, you need to use WHERE only once
To select multiple data to filter means both conditions must satisfy, you can use AND:
SELECT * FROM users
WHERE membership_id <= ‘2000’ AND gender = ‘Male’;

You can use OR if either of the conditions is true or both are true:
SELECT * FROM users
WHERE membership_id <= ‘2000’ OR gender = ‘Male’;

1 Like

i think the += operator is not being used properly for this query

You got it pretty much right but it would be

SELECT * FROM users
WHERE gender = “Female”;

It has to be wrapped in double quotes, not singular

Seems to work with double or single quotes.

1 Like

SELECT * FROM users

WHERE gender = “Male” and membership_id <= 2000;

How would I go about finding the letter of a first or last name using SQL?

WHERE first_name LIKE ‘Le%’;

I just tried

SELECT * FROM users WHERE gender= ‘Female’;

and it works.

SELECT * FROM users;
WHERE gender= ‘Female’;

It doesn’t for some reason I can’ t explain