Database Management — Week 4

Structured Query Language(SQL)Basics

Part 1: Introduction to SQL

The History and Importance of SQL

Structured Query Language, commonly known as SQL, represents the fundamental bridge between human language and database operations. SQL emerged from research conducted at IBM in the early 1970s, when computer scientists recognized the need for a standardized way to interact with relational databases. The language was initially called SEQUEL (Structured English Query Language) and evolved into what we now know as SQL. In 1986, the American National Standards Institute (ANSI) published the first SQL standard, establishing it as the universal language for relational database management systems.

The importance of SQL in today’s data-driven world cannot be overstated. Imagine trying to communicate with someone who speaks a different language without a translator, that’s what attempting to interact with a database would be like without SQL. SQL serves as this translator, allowing us to express our data needs in a structured way that databases can understand and execute. What makes SQL particularly powerful is its declarative nature; we describe what we want the database to do, and the database system figures out the most efficient way to accomplish it. This abstraction means we don’t need to understand the complex internal mechanisms of data storage and retrieval to work effectively with databases.

The Structure of SQL Statements

SQL statements follow a logical structure that combines keywords, clauses, expressions, and predicates to form complete instructions for the database. Keywords are the reserved words that have special meaning in SQL, such as SELECT, FROM, WHERE, and ORDER BY. Clauses are components of statements that perform specific functions, like the WHERE clause that filters data or the ORDER BY clause that sorts results. Expressions combine values, operators, and SQL functions that evaluate to a value, while predicates specify conditions that can be evaluated to true, false, or unknown.

A typical SQL statement reads almost like a natural language sentence, which contributes to its accessibility and longevity. For example, the statement can be understood intuitively:

SELECT name, salary FROM employees WHERE department = 'Sales';

We want to select the name and salary columns from the employees table, but only for those rows where the department equals ‘Sales’. This intuitive structure means that with basic training, even non-programmers can learn to extract meaningful information from databases, making SQL an essential skill across numerous business functions beyond just technical roles.

Categories of SQL Commands

SQL commands can be organized into several logical categories based on their primary functions.

Data Definition Language (DDL) includes commands that define and modify database structures. The CREATE statement builds new database objects like tables, views, and indexes. ALTER modifies existing database structures, such as adding new columns to a table or changing data types. DROP removes database objects entirely. These commands are typically used by database administrators and developers during the design and maintenance phases of database development.

Data Manipulation Language (DML) encompasses commands that work with the actual data within database structures. SELECT retrieves data from one or more tables, INSERT adds new records, UPDATE modifies existing records, and DELETE removes records. These are the commands most frequently used in day-to-day database operations.

Data Control Language (DCL) manages security and permissions through GRANT, which gives users access privileges, and REVOKE, which removes those privileges.

Transaction Control Language (TCL) manages database transactions using COMMIT to save changes permanently, ROLLBACK to undo changes, and SAVEPOINT to set points within transactions that can be rolled back to. Understanding these categories helps us organize our knowledge of SQL and apply the right tools for specific tasks.

Part 2: Querying Data

Retrieving Data with SELECT Statements

The SELECT statement serves as the cornerstone of SQL querying, functioning as the primary mechanism for retrieving data from databases. At its most basic, a SELECT statement specifies which columns we want to retrieve and from which table. When we use the asterisk symbol (*) in a SELECT statement, we’re instructing the database to return all columns from the specified table. For example,

SELECT * FROM employees;

would return every column and every row from the employees table. While this approach is useful for initial exploration of a table’s structure and content, it’s generally considered poor practice in production environments because it can retrieve unnecessary data and impact performance.

In professional database work, we typically specify exactly which columns we need. The statement :

SELECT first_name, last_name, department_id FROM employees;

demonstrates this precise approach, retrieving only three specific columns. This practice makes our intentions clear, improves query performance by reducing the amount of data transferred, and makes our code more maintainable. The SELECT statement can also include expressions and functions that transform or calculate values on the fly. For instance, we could write

SELECT first_name, salary, salary * 1.1 AS proposed_raise;

to calculate a 10% salary increase without actually modifying the underlying data.

Filtering Data with WHERE Clauses

The WHERE clause transforms the SELECT statement from a simple data retrieval tool into a powerful filtering mechanism. By adding a WHERE clause, we can specify exactly which rows should be included in our results based on conditions we define. The basic syntax follows the pattern:

SELECT column_list FROM table_name WHERE condition;

The condition can use various comparison operators to express our filtering criteria. The equals operator (=) finds exact matches, while not equal (<> or !=) excludes specific values. Greater than (>) and less than (<) operators work with numerical data, dates, and even text in some cases.

SQL provides several specialized operators that extend our filtering capabilities.

The BETWEEN operator allows us to specify a range of values, such as “WHERE salary BETWEEN 50000 AND 80000” to find employees with salaries in that range.

The IN operator lets us check if a value matches any in a specified list, like “WHERE department_id IN (10, 20, 30)”.

The LIKE operator enables pattern matching with wildcard characters: the percent sign (%) represents any sequence of characters, and the underscore (_) represents any single character. For example, “WHERE last_name LIKE ‘S%’” would find all last names starting with S.

The IS NULL operator is specifically designed to check for missing data, which cannot be detected using equality operators since NULL represents an unknown value.

Sorting Results with ORDER BY

The ORDER BY clause gives us control over how our query results are presented by specifying the sort order. Without an ORDER BY clause, databases typically return rows in an unpredictable order that may change between executions. By adding “ORDER BY column_name” to our query, we instruct the database to sort the results based on the specified column. By default, ORDER BY sorts in ascending order (from lowest to highest for numbers, A to Z for text), but we can explicitly request descending order using the DESC keyword.

We can sort by multiple columns to create more sophisticated arrangements. For example, “ORDER BY department_id ASC, salary DESC” would first sort employees by department in ascending order, then within each department, sort by salary in descending order. This multi-level sorting is particularly useful for reports where we want to group related records together while maintaining a meaningful order within each group. The ORDER BY clause can also reference columns by their position in the SELECT list rather than by name, such as “ORDER BY 2, 3” to sort by the second and third columns in the results. However, using column names is generally preferred for better code readability and maintainability.

Limiting Results with ROWNUM and FETCH FIRST

When working with large datasets, we often need to limit the number of rows returned by our queries. This limitation serves several purposes: it improves performance by reducing the amount of data processed and transferred, it makes result sets more manageable for human review, and it enables pagination in applications. Oracle provides two primary mechanisms for limiting results: the traditional ROWNUM pseudocolumn and the more modern FETCH FIRST syntax introduced in Oracle 12c.

The ROWNUM approach assigns a sequential number to each row as it’s retrieved, allowing us to filter based on this number. For example,

SELECT * FROM employees WHERE ROWNUM <= 5;

would return only the first five rows. However, ROWNUM has some limitations, particularly when used with ORDER BY clauses, as the numbering occurs before sorting. The newer FETCH FIRST syntax provides a more intuitive and standards-compliant approach. The statement “SELECT first_name, salary FROM employees ORDER BY salary DESC FETCH FIRST 5 ROWS ONLY” clearly expresses our intent to get the top five highest-paid employees. We can also use FETCH FIRST with percentages, such as “FETCH FIRST 10 PERCENT ROWS ONLY” to get the top 10% of rows.

Using Aliases with AS

Column aliases allow us to assign temporary names to columns or expressions in our query results, significantly improving the readability and presentation of our data. The AS keyword, though optional in most SQL implementations, makes our intention clear when assigning aliases. For example,

SELECT first_name AS "Employee Name", salary * 12 AS "Annual Salary" 
FROM employees;

creates more descriptive column headings in the output. Aliases are particularly valuable when working with calculated columns, as they give meaningful names to what would otherwise appear as complex expressions.

When an alias contains spaces or special characters, we must enclose it in double quotes. Without quotes, aliases are typically converted to uppercase and cannot contain spaces. Aliases defined in the SELECT clause can be referenced in the ORDER BY clause, which is useful when sorting by calculated columns. For instance, we could write “SELECT first_name, salary * 12 AS annual_salary FROM employees ORDER BY annual_salary DESC”. However, it’s important to note that we cannot use column aliases in the WHERE clause, as the WHERE clause is logically processed before the SELECT clause where aliases are defined. This order of operations is a common source of confusion for SQL beginners.

Part 3: Hands-on Oracle SQL Practice

Connecting to Oracle Database with Oracle APEX

To begin executing SQL statements, we first need a connection to an Oracle database. Instead of using desktop tools like SQLPlus or SQL Developer, we can use Oracle Application Express (APEX), a browser-based, low-code development platform that includes a powerful SQL interface.

APEX provides a web-based environment accessible from any modern browser. It includes a feature called SQL Workshop, which offers a full suite of tools for running SQL commands, building data models, and managing database objects, all through an intuitive graphical interface.

Get Ayoade Akintayo (PhD)’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Accessing your database via APEX is straightforward. You typically navigate to the APEX instance’s URL (provided by your administrator):

https://iacademy2.oracle.com/ords/f?p=4550:1:1724611845091:::::

Log in with your workspace credentials (username and password), and enter your workspace. Once inside, you navigate to the SQL Workshop menu and open the SQL Commands tool. This is your primary interface for executing ad-hoc SQL statements.

When you use the SQL Commands tool, you are establishing a session with the database server, allowing you to run queries and manage transactions. It’s good practice to verify your connection by running a simple test query like SELECT * FROM dual;. The DUAL table is a special one-row, one-column table present in all Oracle databases, making it perfect for this check. Successfully establishing this connection within APEX is the first step for a streamlined, web-based SQL practice.

Note: You must have completed the Lab on loading the SQL scripts on to your APEX environment

Basic SELECT Statement Practice

Let’s begin with fundamental SELECT statements using the provided examples. The statement “SELECT * FROM employees” serves as our starting point for exploring a table’s complete structure and content. When you run this query, you’ll see all columns and all rows from the employees table. Take note of the different data types present: character data for names, numerical data for IDs and salaries, and possibly date data for hire dates. This comprehensive view helps you understand what information is available before writing more targeted queries.

The next statement, “SELECT first_name, last_name, department_id FROM employees”, demonstrates column-specific selection. Notice how this returns only the three specified columns, making the result set more focused and manageable. As you practice, try modifying this query to include different combinations of columns from the employees table. Pay attention to how the database returns results in a tabular format, with column headers matching the specified column names (or aliases if you used them). This hands-on experience with basic selection builds the foundation for all the more advanced querying techniques we’ll explore.

Practical Filtering Exercises

Now let’s practice filtering with WHERE clauses using the provided examples. Start with “SELECT first_name, salary FROM employees WHERE salary > 5000” to find employees earning more than 5000. Experiment with different comparison operators: try changing > to <, <=, >=, or <> to see how the results change. Then move on to the more complex example: “SELECT first_name, job_id, department_id FROM employees WHERE job_id IN (‘IT_PROG’, ‘HR_REP’)”. This demonstrates how the IN operator can filter for multiple possible values in a single condition.

To deepen your understanding, try creating your own filtering conditions. For example, you might look for employees whose first names start with ‘A’ using “WHERE first_name LIKE ‘A%’”, or find employees with no commission using “WHERE commission_pct IS NULL”. As you experiment with different filters, pay attention to how Oracle handles different data types: text values require single quotes, numbers don’t, and dates have their own formatting requirements. This practical experience will help you develop intuition for how to construct effective filtering conditions.

Sorting and Limiting Results

Practice sorting with the example “SELECT first_name, salary FROM employees ORDER BY salary DESC”. Notice how the results display employees from highest to lowest salary. Try changing DESC to ASC or removing it entirely to see the difference. Then experiment with multi-column sorting by adding a second sort criterion: “ORDER BY department_id ASC, salary DESC”. Observe how this organizes employees by department, then by salary within each department.

Now practice limiting results using both the traditional and modern approaches. Try “SELECT first_name, salary FROM employees WHERE ROWNUM <= 5” followed by “SELECT first_name, salary FROM employees ORDER BY salary DESC FETCH FIRST 5 ROWS ONLY”. Notice the difference in results, the first query returns an arbitrary five employees, while the second returns the five highest-paid employees. This distinction is crucial: when using ROWNUM with ORDER BY, you need to use a subquery to ensure proper ordering before limiting. The FETCH FIRST approach handles this more intuitively.

Real-World Scenario Implementation

Let’s apply our SQL skills to the provided real-world scenarios. For the banking system scenario, enter the query: “SELECT customer_name, account_number, deposit_amount, transaction_date FROM transactions WHERE deposit_amount > 1000000 AND transaction_date >= ADD_MONTHS(SYSDATE, -1) ORDER BY deposit_amount DESC”. This demonstrates multiple concepts: filtering by amount and date range, and sorting results. The ADD_MONTHS function calculates a date one month ago from the current system date.

For the university scenario, implement: “SELECT student_name, department, gpa FROM students WHERE department = ‘Computer Science’ ORDER BY gpa DESC”. This shows simple equality filtering combined with sorting. For the e-commerce scenario: “SELECT product_name, price FROM products ORDER BY price DESC FETCH FIRST 10 ROWS ONLY”. This combines sorting with result limiting to create a “top 10” list. As you run these queries, think about how each clause contributes to the final result and how you might modify them for similar but slightly different business requirements.

Real-Life Scenarios and Applications

Banking System Analysis

In the banking scenario, we’re addressing a common business need: identifying high-value transactions for further analysis or reporting. The query demonstrates several important SQL concepts working together. The WHERE clause uses both a numerical comparison (deposit_amount > 1000000) and a date function (ADD_MONTHS(SYSDATE, -1)) to filter transactions from the last 30 days. The ORDER BY clause organizes the results with the largest deposits first, making it easy for the bank manager to focus on the most significant transactions.

This type of query might be used for various purposes in a banking context: monitoring for money laundering by identifying unusually large deposits, analyzing customer behavior to identify high-net-worth individuals, or simply generating reports for regulatory compliance. The same basic structure could be adapted for other banking analyses, such as finding customers with multiple large withdrawals or identifying accounts with consistently high balances. Understanding how to construct such queries enables financial professionals to extract actionable insights from raw transaction data.

University Academic Management

The university registrar query addresses the need to identify and recognize high-achieving students within specific academic programs. By filtering for the Computer Science department and sorting by GPA in descending order, the query creates a natural ranking of students by academic performance. This information could be used for various purposes: identifying candidates for scholarships or awards, providing data for program assessment, or helping academic advisors identify students who might benefit from advanced opportunities.

In a real university database, we might enhance this query in several ways. We could join with additional tables to include student contact information, major requirements, or course history. We might add additional filters to focus on specific class levels (freshmen, seniors) or students meeting certain credit thresholds. We could also use aggregate functions to calculate department-wide statistics like average GPA. This example demonstrates how even simple SQL queries can provide valuable administrative insights in educational institutions.

E-commerce Business Intelligence

The e-commerce query addresses a fundamental business question: which products represent the premium segment of our offerings? By sorting products by price in descending order and limiting to the top 10 results, the operations manager can quickly identify the most expensive products in the catalog. This information might inform decisions about marketing focus, inventory management, pricing strategy, or supplier relationships.

In a comprehensive e-commerce system, we would likely enhance this query by joining the products table with additional tables containing inventory levels, supplier information, or sales history. We might filter out discontinued products or include only products from specific categories. We could also calculate metrics like profit margin rather than just using retail price. This scenario illustrates how SQL enables data-driven decision making in retail environments, helping businesses understand their product mix and make strategic choices about their offerings.

Hands-on Lab Exercise

Table Creation and Data Insertion

In this comprehensive lab exercise, we’ll create a sample students table, populate it with data, and practice various querying techniques. Begin by executing the CREATE TABLE statement:

CREATE TABLE students (
    student_id NUMBER PRIMARY KEY, 
    student_name VARCHAR2(50), 
    department VARCHAR2(50), 
    gpa NUMBER(3,2)
    );

This statement defines a table with four columns: a numerical student ID that serves as the primary key, a student name up to 50 characters, a department name up to 50 characters, and a GPA stored as a number with three total digits and two decimal places.

Next, insert the sample records using the provided INSERT statements.

INSERT INTO students VALUES (1, 'Sola Aremu', 'Computer Science', 3.75);
INSERT INTO students VALUES (2, 'Dupe Adigun', 'Physics', 3.92);
INSERT INTO students VALUES (3, 'John Nwoko', NULL, 3.45);
INSERT INTO students VALUES (4, 'Muhammed Adamu', 'Mathematics', 2.98);
INSERT INTO students VALUES (5, 'Nanlop Panshak', 'Computer Science', 3.81);

Note: You have to execute each line one after the other and notice that the third record intentionally includes a NULL value for the department column, which will allow us to practice handling missing data.

After inserting these records, run a simple “SELECT * FROM students” to verify that all data was inserted correctly. This end-to-end process, creating a table, populating it with data, and verifying the results, gives you practical experience with the complete data lifecycle.

Comprehensive Query Practice

Now practice the various query types we’ve discussed. Start with “SELECT * FROM students” to retrieve all records, then move to specific column selection with “SELECT student_name, gpa FROM students”. Practice filtering with “SELECT * FROM students WHERE department = ‘Computer Science’” to find only computer science students, then “SELECT * FROM students WHERE gpa > 4.0” to find high-achieving students. Use “SELECT * FROM students WHERE department IS NULL” to find records with missing department values.

Practice sorting with “SELECT student_name, gpa FROM students ORDER BY gpa DESC” to see students ranked by GPA. Experiment with aliases: “SELECT student_name AS Name, gpa AS Grade_Point_Average FROM students”. Try limiting results: “SELECT student_name, gpa FROM students ORDER BY gpa DESC FETCH FIRST 2 ROWS ONLY”. As you work through these exercises, pay attention to the syntax of each query type and how small changes dramatically affect the results. This methodical practice will build muscle memory for the most common SQL patterns.

Assignment: Campus Bookstore Query System

Assignment Overview and Objectives

For this week’s assignment, you’ll design and implement a small database system for a campus bookstore, then write queries to extract meaningful information from it. This practical project brings together all the SQL concepts we’ve covered and applies them to a realistic business scenario. Working in your established groups, you’ll create three tables: books (to track inventory), authors (to track writers), and sales (to record transactions).

Your first task is to design appropriate table structures with meaningful column names and data types. For the books table, you might include columns like book_id, title, author_id, isbn, price, and stock_quantity. The authors table could contain author_id, author_name, and contact_information. The sales table might include sale_id, book_id, sale_date, quantity, and sale_amount. Consider what constraints each column should have, primary keys, foreign keys, NOT NULL constraints, to ensure data integrity.

Implementation and Query Development

After designing your tables, implement them in Oracle SQL Developer using CREATE TABLE statements. Then populate each table with at least five realistic records using INSERT statements. Once your database is prepared, write and test the required queries: display all books and their authors (requiring a JOIN operation), show books with low stock, identify top-selling books, and find prolific authors. Each query should demonstrate specific SQL skills while providing useful business intelligence for the bookstore.

Your final submission should include a complete SQL script file that creates your tables, inserts sample data, and runs all required queries. Include screenshots showing both your table structures (using DESCRIBE) and query results. During your presentation next week, be prepared to explain your design choices and demonstrate how your queries work. This assignment will assess your ability to translate business requirements into a working database design and to use SQL to extract valuable insights from your data.

Reflection Question

SQL as a Bridge from Data to Insight

Our reflection question this week asks: “How does SQL make it possible to extract meaningful insights from raw data, and why is that valuable in everyday business operations?” SQL serves as the essential translation layer between human information needs and computer data storage. Raw data in databases is often fragmented across multiple tables, contains irrelevant or redundant information for any specific purpose, and lacks inherent organization for business analysis. SQL provides the tools to selectively extract, filter, combine, and organize this raw data into meaningful patterns and summaries.

The value of this capability in business operations is immense. Consider a retail manager trying to understand sales trends. Without SQL, they might need to manually scan through thousands of transaction records. With SQL, they can quickly query for top-selling products, identify seasonal patterns, or analyze customer purchasing behavior. In healthcare, SQL enables analysis of patient outcomes across different treatments. In finance, it helps identify fraudulent transactions or assess investment performance. Across every industry, the ability to quickly transform raw data into actionable information supports better decision-making, identifies opportunities and problems early, and provides measurable evidence for strategic planning.

SQL’s declarative nature is particularly powerful because it allows business professionals to focus on what information they need rather than how to technically extract it. This democratization of data access means that marketing managers, financial analysts, operations supervisors, and many other non-technical roles can directly query databases to answer their specific business questions without waiting for specialized technical support. This empowerment leads to more data-driven organizations where decisions are based on evidence rather than intuition alone.

Summary and Key Takeaways

This week we’ve established the foundational knowledge of Structured Query Language that will support all our future database work. We began by understanding SQL’s history and its role as the standard language for relational database communication. We explored the different categories of SQL commands and how they serve distinct purposes in database management. Our deep dive into the SELECT statement revealed how we can retrieve, filter, sort, and limit data to answer specific business questions.

Through hands-on practice with Oracle SQL Developer, we transformed theoretical knowledge into practical skills. We worked with real-world scenarios from banking, education, and e-commerce to understand how SQL applies across different domains. Our interactive activity helped us appreciate the importance of data quality and how SQL can identify data issues. The comprehensive lab exercise built our confidence with end-to-end database operations from table creation through complex querying.

As we move forward in this course, remember that SQL proficiency comes through consistent practice. The basic patterns we learned this week, SELECT, FROM, WHERE, ORDER BY, and limiting results, form the foundation upon which we’ll build more advanced skills including joins, aggregates, subqueries, and data modification. SQL’s power lies in its ability to make vast amounts of data accessible and meaningful, transforming raw information into actionable business intelligence. Whether you pursue a career in database administration, software development, data analysis, or any other technology-related field, these SQL fundamentals will serve as valuable tools in your professional toolkit.