How to Retrieve Data from Databases Using SQL

TL;DR

SQL (structured query language) lets you retrieve, filter, sort, add, update, and delete data in databases without a computer science degree. To start for free, install Microsoft SQL Server plus SQL Server Management Studio (SSMS), then connect to your server and restore a sample database to begin writing queries against real tables.

Transcript

Hi everyone, Kevin here. Today we are going to walk through step-by-step   how you can use SQL or ‘sequel’  for data reporting and analysis. I'm going to assume you have  absolutely no background in SQL,   and we're also going to use all free tools,  so there's really no barrier to learning. By the end of this video, my goal is that you  will know ... Read More

Key Insights

• SQL stands for structured query language, a language used to interact with databases. It lets you retrieve, filter, sort, add, update, and remove data, and anyone can learn it without a computer science degree.
• A database is a collection of tables that have relationships between them. Major websites like Facebook, Twitter, and LinkedIn are all powered by databases that store users, orders, and product information.
• An RDBMS (relational database management system) is the software used to interface with a database. Popular options include Oracle, PostgreSQL, Microsoft SQL Server, and MySQL, and learning one makes picking up others easy.
• Microsoft SQL Server is used in this tutorial because it is one of the most popular options and the tools are completely free while learning. The core SQL concepts carry over to other systems despite minor syntax differences.
• SQL Server offers two free editions: Express, which allows business use but is limited to 10 gigabytes and 4 cores, and Developer, which has full functionality but is restricted to development and testing purposes only.
• SQL Server Management Studio (SSMS) is a graphical front end that lets you write queries against your database. It connects to the server installed separately, and both tools are free to download from Microsoft.
• Databases are split into separate tables to avoid repeating information. Instead of duplicating a customer's full details on every order, an orders table references a customer ID that points to a single customer record.
• The sample KCC database for the Kevin Cookie Company contains four tables: Customers, Order Product, Orders, and Product. Restoring this backup lets learners practice on existing data instead of building from scratch.

Questions & Answers

Q: What does SQL stand for and what is it used for?

SQL stands for structured query language, sometimes pronounced 'sequel.' It is a language you use to interact with databases. With SQL you can retrieve data, filter it, sort it, and also add, update, and remove data from a database. It is particularly useful for pulling insights, such as counting how many people clicked a button on a website to inform product decisions.

Q: Do you need a computer science degree to learn SQL?

No. The video stresses that anyone can learn how to write SQL queries and you definitely do not need a degree in computer science. Once you learn SQL, it becomes another superpower in your toolbox at work. The tutorial assumes absolutely no background in SQL and uses all free tools, so there is really no barrier to learning it.

Q: What is a database and why are major websites built on them?

A database is a collection of tables, and those tables have relationships between them. Any major website, whether Facebook, Twitter, or LinkedIn, is powered by databases. In the video's example, the Kevin Cookie Company uses databases to track its customers, orders, and all of its product information instead of writing everything down by hand on paper.

Q: What is an RDBMS and what are some popular options?

RDBMS stands for relational database management system, the software used to interface with a database. Popular options mentioned include Oracle, PostgreSQL, Microsoft SQL Server, and MySQL, among many others. The video uses Microsoft SQL Server because it is one of the most popular options and free when learning. Once you learn one RDBMS, you can easily pick up any other system since the core concepts are the same.

Q: What is the difference between the Express and Developer editions of SQL Server?

The Express version can be used for business purposes but is more limited: its database can only be up to 10 gigabytes in size and it only supports 4 cores. The Developer version has full functionality but cannot be used for business purposes, only for development and testing. For the tutorial, either edition works fine, and Kevin downloads the Developer edition.

Q: How do you confirm that SQL Server is running after installation?

After installing, you won't see any icons on your taskbar or desktop. To confirm it is running, open the Start menu, click all apps, navigate to Microsoft SQL Server 2019, and open the Configuration Manager. There you can see that SQL Server is currently running, and you can stop, restart, pause, or start the service. The start mode is set to automatic, so the instance starts whenever you boot your PC.

Q: What is SQL Server Management Studio and why is it needed?

SQL Server Management Studio, or SSMS, is a graphical front end that allows you to write queries against your database. The first tool installed provides the server itself, while SSMS lets you connect to that server. After installing and launching it, a prompt asks you to connect to a server using your PC's name, and Windows authentication can be left as the default before clicking connect.

Q: Why is data split into separate tables instead of one big file?

Keeping everything in one file causes information to repeat, such as the same customer name, address, contact details, and cookie name appearing again and again. Splitting data into separate tables avoids this repetition. For example, an orders table can simply reference customer 5, and a separate table holds all of that customer's details. Various IDs connect the tables, which is the core idea behind relational databases.

Summary & Key Takeaways

• SQL is a language for interacting with databases, letting you retrieve, filter, sort, add, update, and delete data. The video focuses on retrieving data, which suits business analysts, product managers, and researchers. It does not cover creating databases, adding tables, or modifying data.

• To follow along for free, install Microsoft SQL Server (Developer or Express edition) and SQL Server Management Studio. The Configuration Manager confirms the server is running and set to start automatically, while SSMS provides a graphical interface to connect to the server.

• Databases store data across related tables rather than one big file to avoid repeating customer and product information. Using IDs, an orders table can reference customer 5 and link to a separate table holding that customer's full contact details, which is the core idea behind relational databases.