4.1 SQL Basics (173m) Flashcards
What is SQL? Can you explain each of the SQL databases below: 1.MySQL, 2.PostgreSQL, 3.Microsoft SQL, 4.Oracle, 5.SQLite? And also can you explain other databases (NoSQL databases) including 1.MongoDB, 2.CouchBase, 3.Redis.
SQL (Structured Query Language) is a programming language used for managing relational databases. It provides a standardized way to interact with databases, allowing users to create, manipulate, and query data stored in the database.
Let’s explore each of the SQL databases you mentioned:
- MySQL: MySQL is an open-source relational database management system (RDBMS). It is widely used and known for its speed, reliability, and ease of use. MySQL supports various operating systems and is commonly used for web applications.
- PostgreSQL: PostgreSQL is also an open-source RDBMS known for its robustness, scalability, and advanced features. It provides support for complex queries, transactions, and concurrency control. PostgreSQL is highly extensible and offers a wide range of data types and indexing capabilities.
- Microsoft SQL Server: Microsoft SQL Server is a relational database management system developed by Microsoft. It is primarily used on Windows platforms and offers a comprehensive set of tools for database management, reporting, and analysis. SQL Server supports enterprise-level applications and provides features like data warehousing, replication, and high availability.
- Oracle: Oracle Database is a widely used RDBMS developed by Oracle Corporation. It is known for its scalability, security, and reliability. Oracle offers advanced features such as partitioning, clustering, and automatic storage management. It is commonly used in large enterprise applications that require high performance and data integrity.
- SQLite: SQLite is a lightweight and embedded RDBMS. It is serverless and operates directly on the local device, making it suitable for mobile and desktop applications. SQLite stores the entire database in a single file, making it easy to manage and deploy. It is widely adopted for small-scale applications and has a small footprint.
Now, let’s move on to the NoSQL databases you mentioned:
- MongoDB: MongoDB is a document-oriented NoSQL database. It stores data in flexible, JSON-like documents, allowing for dynamic schemas. MongoDB is scalable, provides high performance, and supports rich querying capabilities. It is commonly used in scenarios where flexibility and scalability are important, such as web applications and big data environments.
- Couchbase: Couchbase is a distributed NoSQL database designed for high availability and scalability. It combines key-value and document-oriented data models. Couchbase supports a flexible schema and provides features like caching, indexing, and full-text search. It is often used in use cases that require fast data access, real-time analytics, and mobile synchronization.
- Redis: Redis is an in-memory data structure store that can function as a NoSQL database, cache, or message broker. It supports various data structures like strings, lists, sets, and hashes. Redis is known for its high performance, low latency, and advanced data manipulation capabilities. It is frequently used for real-time applications, caching, and session management.
Each of these databases has its strengths and is suitable for different use cases. The choice of a database depends on factors like the nature of data, scalability requirements, performance needs, and specific features required by the application.
Here are examples of popular companies and the databases they use, along with the corresponding backend and frontend technologies they employ:
- MySQL:
- Airbnb: Airbnb, a popular online marketplace for accommodations, uses MySQL as its primary database. They also use a combination of Python and JavaScript for their backend, and React.js for the frontend.
- Pinterest: Pinterest, a visual discovery platform, relies on MySQL for data storage. They utilize Python and Django for their backend, and React.js for the frontend.
- PostgreSQL:
- Apple: Apple, a renowned technology company, utilizes PostgreSQL for various purposes, including storing data for their iTunes and iCloud services. They have a proprietary backend technology stack and use various frontend frameworks depending on the product.
- Instagram: Instagram, a widely-used social media platform, uses PostgreSQL as their primary database for storing user data, posts, and interactions. They employ Python and Django for their backend, and React.js for the frontend.
- Microsoft SQL Server:
- Stack Overflow: Stack Overflow, a popular question-and-answer platform for programmers, relies on Microsoft SQL Server as its backend database. They use C# and .NET for their backend, and JavaScript (with jQuery) for the frontend.
- Expedia: Expedia, an online travel agency, employs Microsoft SQL Server for their data storage needs. They use a combination of Java and .NET for their backend, and JavaScript (with React.js) for the frontend.
- Oracle:
- Amazon: Amazon, a multinational technology company, uses Oracle databases for some of their critical systems, including the order management system. They have a proprietary backend technology stack and use various frontend frameworks depending on the product.
- Bank of America: Bank of America, a leading financial institution, utilizes Oracle databases for managing their banking systems. They have a custom-built backend system and use various frontend technologies for different applications.
- SQLite:
- WhatsApp: WhatsApp, a popular messaging application, utilizes SQLite for storing message data on users’ devices. They use Erlang for their backend and React Native for the frontend.
- Firefox: Mozilla Firefox, a widely-used web browser, employs SQLite for various purposes, including storing browser history and bookmarks. They use a combination of C++ and JavaScript for their backend, and HTML, CSS, and JavaScript for the frontend.
Moving on to NoSQL databases:
- MongoDB:
- Airbnb: In addition to MySQL, Airbnb also uses MongoDB for certain use cases, such as logging and user activity tracking. They primarily employ Python and JavaScript for their backend, and React.js for the frontend.
- The New York Times: The New York Times, a prominent media company, uses MongoDB to store and manage article metadata, including tags and annotations. They have a custom-built backend system and use various frontend technologies for different applications.
- Couchbase:
- LinkedIn: LinkedIn, a professional networking platform, uses Couchbase for its distributed data caching layer. They primarily use Java and Scala for their backend, and JavaScript (with React.js) for the frontend.
- Verizon: Verizon, a telecommunications company, employs Couchbase for caching and high-performance data storage. They have a custom-built backend system and use various frontend technologies for different applications.
- Redis:
- Twitter: Twitter, a popular social media platform, uses Redis for caching and managing real-time data, such as tweets and user timelines. They primarily use Scala and Java for their backend, and JavaScript (with React.js) for the frontend.
- Pinterest: Alongside MySQL, Pinterest also uses Redis for various caching needs, including caching user feeds and search results. They utilize Python and Django for their backend, and React.js for the frontend.
It’s worth noting that the technology stacks and databases used by
these companies may evolve over time, and they may employ a combination of other technologies based on their specific requirements.
What is database Schema?
A database schema is a logical structure or blueprint that defines how data is organized, stored, and related within a database. It represents the overall design and organization of the database and provides a framework for creating and managing tables, relationships, constraints, and other database objects.
A database schema typically includes the following components:
- Tables: A table represents a collection of related data and is the fundamental building block of a database schema. Tables consist of rows (records) and columns (fields) that define the structure and attributes of the data stored in the database.
- Columns: Columns define the individual data elements or attributes within a table. Each column has a name, data type, and optionally, constraints that enforce rules on the data stored in that column (e.g., uniqueness, data format, foreign key references).
- Relationships: Relationships establish connections between tables based on common data values. The most common type of relationship is a foreign key relationship, where a column in one table refers to the primary key column of another table, creating a link between the two tables.
- Constraints: Constraints define rules and restrictions on the data stored in the database. They ensure data integrity and enforce certain conditions, such as uniqueness, referential integrity, and data validation.
- Indexes: Indexes are data structures that enhance data retrieval performance by providing quick access to specific data within a table. They improve query performance by creating a sorted representation of the data based on one or more columns.
- Views: Views are virtual tables that are derived from the underlying tables in the database. They present a customized or filtered view of the data to users or applications, simplifying data access and providing an additional layer of security.
By defining a database schema, developers and database administrators establish the structure, relationships, and rules that govern how data is stored, organized, and accessed within the database system. The schema acts as a blueprint for creating and managing the database, ensuring consistency, data integrity, and efficient data retrieval.
Example:
Sure! Let’s consider an example of a simple database schema for a fictional e-commerce website.
- Tables:
- Customers: This table stores information about the customers of the e-commerce website. It may include columns such as customer ID, name, email, address, and phone number.
\+----+--------------+-----------------+-------------------+--------------+ | ID | Name | Email | Address | Phone | \+----+--------------+-----------------+-------------------+--------------+ | 1 | John Doe | john@example.com | 123 Main St | 555-123-4567 | | 2 | Jane Smith | jane@example.com | 456 Elm St | 555-987-6543 | \+----+--------------+-----------------+-------------------+--------------+
- Products: This table stores information about the products available on the website. It may include columns such as product ID, name, description, price, and quantity in stock.
\+----+-----------------+----------------------------------+-------+--------+ | ID | Name | Description | Price | Stock | \+----+-----------------+----------------------------------+-------+--------+ | 1 | T-shirt | Soft cotton t-shirt | 19.99 | 100 | | 2 | Jeans | Slim-fit denim jeans | 49.99 | 50 | \+----+-----------------+----------------------------------+-------+--------+
- Relationships:
- Orders: This table represents customer orders and establishes a relationship with the Customers and Products tables. It may include columns such as order ID, customer ID (foreign key referencing the Customers table), product ID (foreign key referencing the Products table), quantity, and order date.
\+----+-------------+------------+----------+------------+ | ID | Customer_ID | Product_ID | Quantity | Order_Date | \+----+-------------+------------+----------+------------+ | 1 | 1 | 1 | 2 | 2023-06-01 | | 2 | 1 | 2 | 1 | 2023-06-05 | \+----+-------------+------------+----------+------------+
- Constraints:
- Unique Constraint: The email column in the Customers table can have a unique constraint, ensuring that each customer has a unique email address.
\+----+--------------+-----------------+-------------------+--------------+ | ID | Name | Email | Address | Phone | \+----+--------------+-----------------+-------------------+--------------+ | 1 | John Doe | john@example.com | 123 Main St | 555-123-4567 | | 2 | Jane Smith | jane@example.com | 456 Elm St | 555-987-6543 | \+----+--------------+-----------------+-------------------+--------------+
- Foreign Key Constraint: The Customer_ID column in the Orders table references the ID column in the Customers table, ensuring that only valid customer IDs can be inserted into the Orders table.
```
+—-+————-+————+———-+————+
| ID | Customer_ID | Product_ID | Quantity | Order_Date |
+—-+————-+————+———-+————+
| 1 | 1 | 1 | 2 | 2023-06-01 |
|
2 | 1 | 2 | 1 | 2023-06-05 |
+—-+————-+————+———-+————+
```
This is a simplified example of a database schema for an e-commerce website. The schema defines the structure and relationships between tables, allowing for efficient storage, retrieval, and management of data within the database.
Give a comprehensible list of data types and examples for each in SQL database?
Certainly! Here is a list of common data types used in SQL databases, along with examples for each type:
- 1.Integer:
- Examples: 1, 10, -5, 1000
- 2.Decimal/Numeric:
- Examples: 3.14, 2.5, -0.75, 100.99
- 3.Char:
- Fixed-length character string.
- Examples: ‘Hello’, ‘OpenAI’, ‘SQL’
- 4.Varchar:
- Variable-length character string.
- Examples: ‘John Doe’, ‘example@gmail.com’, ‘Product XYZ’
- 5.Text:
- Variable-length character string for larger text blocks.
- Examples: ‘Lorem ipsum dolor sit amet…’, ‘Description of the article…’
- 6.Date:
- Stores a date.
- Examples: ‘2023-06-25’, ‘1990-12-31’
- 7.Time:
- Stores a time value.
- Examples: ‘10:30:00’, ‘18:45:23’
- 8.DateTime/Timestamp:
- Stores both date and time.
- Examples: ‘2023-06-25 14:30:00’, ‘2022-01-01 00:00:00’
- 9.Boolean:
- Represents true or false.
- Examples: true, false
- 10.Binary:
- Stores binary data, such as images or files.
- Examples: 0x2550AABB, 0b11010101
- 11.Float/Real:
- Floating-point number with decimal precision.
- Examples: 3.1415, 2.71828, -1.2345
- 12.Double Precision:
- Double-precision floating-point number.
- Examples: 3.14159265359, 1.23456789012
- 13.Enum:
- A predefined set of values that can be chosen.
- Examples: ‘Male’, ‘Female’, ‘Other’
- 14.JSON:
- Stores JSON (JavaScript Object Notation) data.
- Examples: ‘{“name”: “John”, “age”: 30}’, ‘[1, 2, 3]’
- 15.XML:
- Stores XML (eXtensible Markup Language) data.
- Examples:
'<person><name>John</name><age>30</age></person>'
These are just some of the common data types used in SQL databases. Different database management systems may offer additional data types or variations of these types. It’s important to consult the documentation of the specific database system you are working with to understand the available data types and their specific characteristics and usage.
What does the following code do in SQL? Explain the code completely.
SELECT * FROM books;
The SQL code you provided, SELECT * FROM books;, is a query that retrieves all the data from the “books” table in a database.
Let’s break down the code and explain its components:
-
SELECT: This keyword is used to specify the columns or data that you want to retrieve from the database. In this case, the*(asterisk) is used as a shorthand notation to select all columns in the “books” table. -
FROM: This keyword indicates the table from which you want to retrieve the data. In this case, the table is named “books”. -
books: This is the name of the table from which the data will be fetched. It is the identifier that represents the specific table within the database.
Therefore, the code SELECT * FROM books; instructs the database management system to retrieve all the rows and columns from the “books” table and return the result set containing all the data stored in that table. The * in the SELECT clause denotes that all columns in the “books” table should be included in the query result.
Treehouse Notes:
Definitions
* Syntax: The vocabulary and grammatical rules surrounding the structure of your code.
* Keywords: The vocabulary words of a programming language used to issue commands to a computer.
SQL Used:
~~~
SELECT * FROM <table name>;
~~~
* The asterisk or star symbol (*) means all columns.
* The semi-colon (;) terminates the statement like a period in a sentence or a question mark in a question.
Examples:
~~~
SELECT * FROM books;
SELECT * FROM products;
SELECT * FROM users;
SELECT * FROM countries;
~~~
Challenge 1/5: In this code challenge you have an eCommerce database full of tables that you might expect to see in a real database.
Write a SQL query to answer the following question. What are all the products in the products table?
Challenge 2/5: Write the SQL to retrieve all customer information from the users table.
Challenge 3/5: Select all addresses from the customer_addresses table.
Challenge 4/5: We’re using a different database now. Did you know mobile phones have databases? Find all the contacts in this smartphone database. All the contacts are in a table called phone_book.
Challenge 5/5: In the Yorkshire Division Four in Rugby, the team Hessle RUFC have a website that shows their latest matches. Their database holds a results table that stores their latest wins and losses. Why not have a look at their latest results yourself!?
Solution to Challenges:
~~~
SELECT * FROM products; # challenge 1
SELECT * FROM users; # challenge 2
SELECT * FROM customer_addresses; # challenge 3
SELECT * FROM phone_book; # challenge 4
SELECT * FROM results; # challenge 5
~~~
Select is a keyword; From is a keyword.* indicates everything within the table.; indicates closing of the command.
How will you select a column from a table in SQL?
Answer:
SELECT * FROM patrons;
* indicates to select all columns.
SELECT email FROM patrons;
If you want to select email or first_name column, you can simply put it instead of astericks.
You can also select two or more columns like this:SELECT first_name, email FROM patrons;
Treehouse Notes:
SQL Used
Retrieving a single column:
~~~
SELECT <column> FROM <table name>;
~~~</column>
Examples:
~~~
SELECT email FROM users;
SELECT first_name FROM users;
SELECT name FROM products;
SELECT zip_code FROM addresses;
~~~
Retrieving multiple columns:
~~~
SELECT <column name 1>, <column name 2>, … FROM <table name>;
~~~
Examples:
~~~
SELECT first_name, last_name FROM customers;
SELECT name, description, price FROM products;
SELECT title, author, isbn, year_released FROM books;
SELECT name, species, legs FROM pets;
~~~
Challenge 1: Here are the columns in a users table in an e-commerce site: id, username, password, first_name, last_name.
Run a SQL query to get the two columns you’d need to generate the full names of each customer.
Challenge 2: We’re still in the e-commerce database. This time, from the products table, get the name of every product.
Challenge 3: In the e-commerce database there’s a customer_addresses table with the following columns: id, nickname, street, city, state, zip, user_id.
Select all the columns that are to do with the address. For example, all columns except id, nickname and user_id.
Challenge 4: We’re using a database on a smartphone again. We have a phone_book table. In here there’s an id, first_name, last_name and phone.
As the user types the phone number in we want to show possible autocomplete values. Bring back only the phone numbers of each contact only. Our smartphone can work out which of the results to show.
Challenge 5: We’re still using the phone_book table. Remember it has the columns of id, first_name, last_name and phone.
Imagine a user is typing someone’s last name in a search facility on the phone. As the user types, suggestions will appear on the screen. Bring back both the first name and last name for every person in the phone book. The phone will filter the appropriate suggestions.
Answer to the challenges:
SELECT first_name, last_name FROM users; #challenge 1 SELECT name FROM products; #challenge 2 SELECT street, city, state, zip FROM customer_addresses; #challenge 3 SELECT phone FROM phone_book; #challenge 4 SELECT first_name, last_name FROM phone_book; #challenge 5
From books database, using sql show only title and first_published. The title column should appear as “Title” and first_published column should appear as “First Published”.
It can be written as:
~~~
SELECT title AS Title, first_published AS “First Published” FROM books;
~~~
or can also be written as:
~~~
SELECT title Title, first_published “First Published” FROM books;
~~~
TreeHouse Notes:
SQL Used
~~~
SELECT <column> AS <alias> FROM <table name>;
SELECT <column> <alias> FROM <table name>;
~~~</alias></column></alias></column>
Examples:
~~~
SELECT username AS Username, first_name AS “First Name” FROM users;
SELECT title AS Title, year AS “Year Released” FROM movies;
SELECT name AS Name, description AS Description, price AS “Current Price” FROM products;
SELECT name Name, description Description, price “Current Price” FROM products;
~~~
Challenge 1:
We’re back in our e-commerce database. There’s a products table with the columns id, name, description and price.
Can you retrieve both the name and description aliased as “Product Name” and “Product Description”.
Challenge 2:
In the users table we have columns of id, username, password, first_name and last_name.
Select the username and the first and last names and alias them as “Username”, “First Name” and “Last Name”.
Challenge 3:
We’re now back with the smartphone database. In the phone_book we have the columns id, first_name, last_name and phone.
Alias the first and last names and phone as “First Name”, “Last Name” and “Phone Number”.
Challenge 4:
In this sports team database there’s a results table with the columns of id, home_team, home_score, away_team, away_score and played_on.
Alias “Home Team”, “Home Score”, “Away Team”, “Away Score” and “Date Played” to the appropriate columns.
Challenge 1:
~~~
SELECT name AS “Product Name”, description AS “Product Description” FROM products;
~~~
Challenge 2:
~~~
SELECT username AS “Username”, first_name AS “First Name”, last_name AS “Last Name” FROM users;
~~~
Challenge 3:
~~~
SELECT first_name AS “First Name”, last_name AS “Last Name”, phone AS “Phone Number” FROM phone_book;
~~~
Challenge 4:
~~~
SELECT home_team AS “Home Team”, home_score AS “Home Score”, away_team AS “Away Team”, away_score AS “Away Score”, played_on AS “Date Played” FROM results;
~~~
Note: SELECT, FROM, AS all can be both lower or upper case, but to distinguish it from the other names we use Upper case as its industry best practice.
Explain the following SQL codes:
-- What are all the books authored by J.K. Rowling and what year were they first published? --- SELECT <columns> FROM <table> WHERE <column> <operator> <value>; SELECT title, author FROM books WHERE first_published = 1997; SELECT title, author FROM books WHERE first_published = 1997 or first_published = 2003 ; SELECT title, first_published FROM books WHERE author = "J.K. Rowling"; -- What books were not authored by J.K. Rowling? --- SELECT <columns> FROM <table> WHERE <column> <operator> <value>; SELECT title, author, first_published FROM books WHERE author != "J.K. Rowling"; -- What books were loaned on the 10th of December 2015? SELECT book_id FROM loans WHERE loaned_on = "2015-12-10"; -- Which book is book 15? SELECT title FROM books WHERE id = 15; SELECT * FROM loans;
Note: = is case sensitive in SQL, in other coding languages instead of = they use ==.
CheatSheet:
~~~
— Syntax for finding or excluding exact matches
— SELECT <columns> FROM <table> WHERE <column> <operator> <value>;
SELECT title, author FROM books WHERE first_published = 1997;
SELECT title, first_published FROM books WHERE author = "J.K. Rowling";
SELECT title, author, first_published FROM books WHERE author != "J.K. Rowling";
SELECT book_id FROM loans WHERE loaned_on = "2015-12-10";
SELECT title FROM books WHERE id = 15;
~~~</value></operator></column></columns>
Certainly! Here’s a breakdown of the SQL codes with explanations included:
```sql
– What are all the books authored by J.K. Rowling and what year were they first published?
– SELECT <columns> FROM <table> WHERE <column> <operator> <value>;</value></operator></column></columns>
SELECT title, author FROM books WHERE first_published = 1997;
~~~
This code retrieves the titles and authors of books from the “books” table where the “first_published” column has a value of 1997. It will return all books authored by J.K. Rowling and published in 1997.
```sql
SELECT title, author FROM books WHERE first_published = 1997 or first_published = 2003 ;
~~~
This code is similar to the previous one, but it retrieves books that were first published either in 1997 or 2003. The “or” operator is used to combine multiple conditions.
```sql
SELECT title, first_published FROM books WHERE author = “J.K. Rowling”;
~~~
This code selects the title and first_published columns from the “books” table where the author is “J.K. Rowling”. It returns the titles and publication years of books authored by J.K. Rowling.
```sql
– What books were not authored by J.K. Rowling?
– SELECT <columns> FROM <table> WHERE <column> <operator> <value>;</value></operator></column></columns>
SELECT title, author, first_published FROM books WHERE author != “J.K. Rowling”;
~~~
This code selects the title, author, and first_published columns from the “books” table where the author is not equal to “J.K. Rowling”. The “!=” operator is used to denote inequality. It returns the books that were authored by someone other than J.K. Rowling.
```sql
– What books were loaned on the 10th of December 2015?
SELECT book_id FROM loans WHERE loaned_on = “2015-12-10”;
~~~
This code retrieves the book_id column from the “loans” table where the “loaned_on” column has a value of “2015-12-10”. It returns the book IDs of the books that were loaned on the 10th of December 2015.
```sql
– Which book is book 15?
SELECT title FROM books WHERE id = 15;
~~~
This code selects the title column from the “books” table where the “id” column has a value of 15. It returns the title of the book with the ID of 15.
```sql
SELECT * FROM loans;
~~~
This code selects all columns from the “loans” table. It retrieves all the loan records in the table, including information about the books loaned and the dates of the loans.
Note: In SQL, the equality operator is “=” and not “==”. The double equals sign (“==”) is typically used in programming languages like Python or JavaScript.
Treehouse Notes:
SQL Used
A WHERE Clause
~~~
SELECT <columns> FROM <table> WHERE <condition>;
~~~</condition></columns>
Equality Operator
Find all rows that a given value matches a column’s value.
~~~
SELECT <columns> FROM <table> WHERE <column> = <value>;
~~~</value></column></columns>
Examples:
~~~
SELECT * FROM contacts WHERE first_name = “Andrew”;
SELECT first_name, email FROM users WHERE last_name = “Chalkley”;
SELECT name AS “Product Name” FROM products WHERE stock_count = 0;
SELECT title “Book Title” FROM books WHERE year_published = 1999;
~~~
Inequality Operator
Find all rows that a given value doesn’t match a column’s value.
~~~
SELECT <columns> FROM <table> WHERE <column> != <value>;
SELECT <columns> FROM <table> WHERE <column> <> <value>;
~~~
The not equal to, or inequality operator, can be written in two ways != and <>. The latter is less common.</value></column></columns></value></column></columns>
Examples:
~~~
SELECT * FROM contacts WHERE first_name != “Kenneth”;
SELECT first_name, email FROM users WHERE last_name != “L:one”;
SELECT name AS “Product Name” FROM products WHERE stock_count != 0;
SELECT title “Book Title” FROM books WHERE year_published != 2015;
~~~
See all of the SQL used in SQL Basics in the SQL Basics Cheat Sheet.
Challenge 1: In our e-commerce database we have a users table with the columns id, username, password, first_name and last_name.
Write a SQL query that retrieves the first and last names only where the username is equal to “wig_lady”.
Challenge 2: In the products table we have the columns id, name, description and price.
Find all products that don’t have the price of 9.99. Include all columns.
Challenge 3: From the users table, find all the username fields with the last_name of “Chalkley”. Only return the usernames.
Challenge 1:
~~~
SELECT first_name, last_name FROM users WHERE username = “wig_lady”;
~~~
Challenge 2:
~~~
SELECT * FROM products WHERE price != 9.99;
~~~
Challenge 3:
~~~
SELECT username FROM users WHERE last_name = “Chalkley”;
~~~
Explain the following code and also explain SQL operators:
-- What books are in the library that were first published after the year 2005? SELECT * FROM books WHERE first_published >= "2005";
The given code is an SQL query that retrieves information from a table called “books” based on a specific condition. Let’s break it down step by step:
-
SELECT *: This is the statement that indicates the query will retrieve all columns from the specified table. -
FROM books: This specifies the table name from which the data will be retrieved. In this case, the table is named “books”. -
WHERE first_published >= "2005": This is the condition applied to filter the rows in the result set. It specifies that only the rows where the value of the “first_published” column is greater than or equal to the year 2005 should be included in the result.
The code is querying the database to find all books in the library that were first published after the year 2005. It will return all the columns (denoted by “*”) from the “books” table where the “first_published” column has a value greater than or equal to “2005”.
SQL operators are used to perform various operations in SQL queries. Some common SQL operators include:
- Comparison Operators: These operators are used to compare values. Examples include:
-
=(equal to) -
<>or!=(not equal to) -
<(less than) -
>(greater than) -
<=(less than or equal to) -
>=(greater than or equal to)
-
- Logical Operators: These operators are used to combine conditions or negate them. Examples include:
-
AND(logical AND) -
OR(logical OR) -
NOT(logical NOT)
-
- Arithmetic Operators: These operators are used for mathematical calculations. Examples include:
-
+(addition) -
-(subtraction) -
*(multiplication) -
/(division) -
%(modulus)
-
- String Operators: These operators are used to manipulate strings. Examples include:
-
||(concatenation) -
LIKE(pattern matching)
-
These operators allow you to perform a wide range of operations and conditions in SQL queries, enabling you to retrieve and manipulate data effectively.
Treehouse Notes:
Here are the relational operators as bullet points and the SQL code as code snippets with important words in bold:
Relational Operators:
- < less than
- <= less than or equal to
- > greater than
- >= greater than or equal to
SQL Used:
~~~
SELECT <columns> FROM <table> WHERE <column> < <value>;
SELECT <columns> FROM <table> WHERE <column> <= <value>;
SELECT <columns> FROM <table> WHERE <column> > <value>;
SELECT <columns> FROM <table> WHERE <column> >= <value>;
~~~</value></column></columns></value></column></columns></value></column></columns></value></column></columns>
Examples:
SELECT first_name, last_name FROM users WHERE date_of_birth < '1998-12-01'; SELECT title AS "Book Title", author AS Author FROM books WHERE year_released <= 2015; SELECT name, description FROM products WHERE price > 9.99; SELECT title FROM movies WHERE release_year >= 2000;
See all of the SQL used in SQL Basics in the SQL Basics Cheat Sheet.
Challenge 1: We have a database that runs a sports team’s website. In the results table we have the columns id, home_team, home_score, away_team, away_score, played_on.
Find all results where the home team’s score is above 12.
Challenge 2: We’re still using the sports team’s database. In the results table we have the columns id, home_team, home_score, away_team, away_score, played_on.
Find all results where the away team’s score is lower than 10.
Challenge 3: We’re back using the e-commerce database. I only have 10.99 left in my bank account. Write a query that will return all products from the products table that I can afford.
The columns in the products are id, name, description and price.
Challenge 1:
~~~
SELECT * FROM results WHERE home_score > 12;
~~~
Challenge 2:
~~~
SELECT * FROM results WHERE away_score < 10;
~~~
Challenge 3:
~~~
SELECT * FROM products WHERE price <= 10.99;
~~~
Explain the following code:
-- SELECT <columns> FROM <table> WHERE <condition 1> AND/OR <condition 2>; -- What books in our library were authored by J.K. Rowling before the year 2000? SELECT title FROM books WHERE author = "J.K. Rowling" and first_published < "2000"; -- SELECT <columns> FROM <table> WHERE <condition 1> AND/OR <condition 2>; -- What books were either authored by J.K. Rowling or published before thr year 2000? SELECT title FROM books WHERE author = "J.K Rowling" OR first_published < "2000"; -- SELECT <columns> FROM <table> WHERE <condition 1> AND/OR <condition 2>; -- What books do we have in the library authored by "Ernest Cline" or "Andy Weir"? SELECT title, author FROM books WHERE author = "Ernest Cline" OR author = "Andy Weir";
The given code consists of multiple SQL queries with different conditions. Let’s break down each query step by step and explain its purpose:
- Query: What books in our library were authored by J.K. Rowling before the year 2000?
sql SELECT title FROM books WHERE author = "J.K. Rowling" AND first_published < "2000";
This query selects the “title” column from the “books” table. It specifies the condition that the author must be “J.K. Rowling” and the “first_published” year must be less than “2000”. TheANDoperator combines the two conditions, meaning both conditions must be true for a row to be included in the result set. - Query: What books were either authored by J.K. Rowling or published before the year 2000?
sql SELECT title FROM books WHERE author = "J.K Rowling" OR first_published < "2000";
This query also selects the “title” column from the “books” table. It specifies two conditions. The first condition is that the author must be “J.K. Rowling”, and the second condition is that the “first_published” year must be less than “2000”. TheORoperator combines the conditions, meaning a row will be included in the result set if either condition is true. - Query: What books do we have in the library authored by “Ernest Cline” or “Andy Weir”?
sql SELECT title, author FROM books WHERE author = "Ernest Cline" OR author = "Andy Weir";
This query selects both the “title” and “author” columns from the “books” table. It specifies two conditions using theORoperator. It searches for rows where the author is either “Ernest Cline” or “Andy Weir”. If either condition is true, the row will be included in the result set.
In summary, these queries demonstrate the usage of conditions in SQL queries. They allow you to filter data based on specific criteria using logical operators such as AND and OR.
CHEATSHEETS:
~~~
– Syntact for multiple conditions
– SELECT <columns> FROM <table> WHERE <condition 1> AND/OR <condition 2>;</columns>
SELECT title FROM books WHERE author = “J.K. Rowling” AND first_published < 2000;
SELECT title FROM books WHERE author = “J.K. Rowling” OR first_published < 2000;
SELECT title FROM books WHERE author = “Ernest Cline” OR author = “Andy Weir”;
SELECT title FROM books WHERE author = “Ernest Cline” AND author = “Andy Weir”; – No results
~~~
Treehouse Notes:
SQL Used:
- You can compare multiple values in a WHERE condition. If you want to test that both conditions are true, use the AND keyword, or if you want to test that either condition is true, use the OR keyword.
```sql
SELECT <columns> FROM <table> WHERE <condition 1> AND <condition 2> ...;
SELECT <columns> FROM <table> WHERE <condition 1> OR <condition 2> ...;
~~~</columns></columns>
Examples:
```sql
SELECT username FROM users WHERE last_name = “Chalkley” AND first_name = “Andrew”;
SELECT * FROM products WHERE category = “Games Consoles” AND price < 400;
SELECT * FROM movies WHERE title = “The Matrix” OR title = “The Matrix Reloaded” OR title = “The Matrix Revolutions”;
SELECT country FROM countries WHERE population < 1000000 OR population > 100000000;
~~~
See all of the SQL used in SQL Basics in the SQL Basics Cheat Sheet.
Challenge 1: We’re back in the sports team database. There’s a results table with the columns id, home_team, home_score, away_team, away_score and played_on .
Find all the matches in the results table where “Hessle” was playing away as the away team and their score was above 18 points.
Challenge 2: Now we’re in the e-commerce database. In the users table we have the columns id, username, password, first_name and last_name.
Find all users with either the last name “Hinkley” or “Pettit”
Challenge 1:
~~~
SELECT * FROM results WHERE away_team = “Hessle” AND away_score > 18;
~~~
Challenge 2:
~~~
SELECT * FROM users WHERE last_name = “Hinkley” OR last_name = “Pettit”;
~~~
