Course Overview:

This comprehensive 6-month course is designed to take you from a beginner to an advanced level in SQL (Structured Query Language). SQL is a fundamental tool for managing and manipulating relational databases, and mastering it can open up numerous career opportunities in data analysis, database administration, and software development. Throughout this course, you will learn SQL syntax, database design principles, advanced querying techniques, and practical applications through hands-on projects.

The SQL data manipulation language (DML) is used to query and modify database data. In this chapter, we will describe how to use the SELECT, INSERT, UPDATE, and DELETE SQL DML command statements, defined below.

SELECT  – to query data in the database

INSERT  – to insert data into a table

UPDATE – to update data in a table

DELETE – to delete data from a table

In the SQL DML statement:

• Each clause in a statement should begin on a new line.

• The beginning of each clause should line up with the beginning of other clauses.

• If a clause has several parts, they should appear on separate lines and be indented under the start of the clause to show the relationship.

• Upper case letters are used to represent reserved words.

• Lower case letters are used to represent user-defined words.

Terms You Need to Understand

• The WHERE clause

• The ORDER BY clause

• Filtering

• Sorting

• Comparison condition

• Logical condition

• Top-N query

• Ascending sort

• Descending sort

Concepts You Need to Master

• Filtered queries

• Sorted queries

• Precedence of logical conditions

• What the available comparison conditions are

• What the available logical conditions are

• Comparison conditions compare expressions

• Logical conditions allow for multiple comparisons

• What equi, anti, and range comparison conditions are

• The use of LIKE, IN, EXISTS, BETWEEN, ANY, SOME, and ALL comparison conditions

• Logical condition precedence: (), NOT, AND, OR

• NULL values and sorting

• Sorting methods

This chapter covers filtering of rows using the WHERE clause and sorting of retrieved data using the ORDER BY clause. The WHERE clause applies to both queries and most DML commands; the ORDER BY clause applies to queries only.

Author Bio: 

Ben Richardson is the owner of Acuity Training, an IT training company based in the UK. Acuity is one of the UK’s leading SQL training companies. For more details click here.

Sub-queries are queries within another query.  The result of the inner sub-query is fed to the outer query, which uses that to produce its outcome. If that outer query is itself the inner query to a further query then the query will continue until the final outer query completes.

There are two types of subqueries in SQL however, correlated sub-queries and uncorrelated subqueries. Let’s take a look at these.

Normalization should be part of the database design process. However, it is difficult to separate the normalization process from the (ER) modeling process so the two techniques should be used concurrently.

Use an entity relation diagram (ERD) to provide the big picture, or macro view, of an organization’s data requirements and operations. This is created through an iterative process that involves identifying relevant entities, their attributes and their relationships.

Normalization procedure focuses on characteristics of specific entities and represents the micro view of entities within the (ERD).

Constraints are a very important feature in a relational model. In fact, the relational model supports the well-defined theory of constraints on attributes or tables. Constraints are useful because they allow a designer to specify the semantics of data in the database. Constraints are the rules that force DBMSs to check that data satisfies the semantics.