Working with Azure SQL Database using Query Editor
In this video, we’ll be introduced to the use of SQL and the Query Editor within Microsoft Azure to interact with our relational database(s).
Azure SQL databases are relational and follow the common tabular format presented earlier this week.
Server vs database
The server is the individual machine (or virtual machine) that stores the database(s). It’s theoretically possible to spread one database across many servers, but this can cause performance and management issues.
Schema vs table
Schema refers to the specific organisation the database has been set up in. It tells the database management system (DBMS) the names of the tables, the headers of the columns, and which are primary and foreign keys. This can be represented visually, or in a set of formulas that describe the constraints, often written in Structured Query Language (SQL).
Common SQL commands
SQL (pronounced see-kwil or ess-kew-el) is a programming & computation language commonly used for extracting data from or adding data to a database. Some common commands seen in the video are:
SELECT column1, column2, column3– designates which column(s) you wish to see from a table (or tables). An asterisk (*) can be used to designate ‘all’ columns.FROM table_name;– designates which table(s) you wish to apply theSELECTquery to within the database.
- Note the semi-colon after the table’s name; it’s not always necessary but is advised to be present after each SQL query.
WHERE– this clause is used to filter your results from theSELECT(or other queries) by excluding results that do not fit the given criteria.
- For numeric values, simple statements such as
WHERE column1=3;will return only results that have a value of 3 in that first column. - For comparing text values, quotations are needed.
WHERE column2='London' OR 'Cape Town';will only return results where the content of the second column is either London or Cape Town.
- For numeric values, simple statements such as
AS– this is used to create an alias for a column or a table. This is useful for making your results easier to read and interpret.
SELECT column_name(s) FROM table_name AS alias_name;- This alias is only temporary, and will only exist for as long as you run the SQL query.
JOIN– is a clause used to combine records/rows from two or more tables, based on a related column between them.
SQL supports the following comparative operators:
| Operator | Descriptor |
|---|---|
= |
Equal |
> |
Greater than |
< |
Less than |
>= |
Greater than or equal |
<= |
Less than or equal |
<> |
Not equal. Note: In some versions of SQL, this operator may be written as **!= |
BETWEEN |
Between a certain range |
LIKE |
Search for a pattern |
IN |
To specify multiple possible values for a column |
Note: SQL keywords are NOT case sensitive; select is the same as SELECT. Popular convention dictates that the query commands are written in capital letters to make it easier to read a query, but not necessary for it to function.
Square brackets in queries
[ ] are used when keywords or special characters are in the column names or identifiers. For example, SELECT First Name FROM table1; wouldn’t work, as there’s a blank space between First and Name. The correct query would be SELECT [First Name] FROM table1;.Note: Why not explore futher the new SQL commands introduced in this step? You can find more information at W3Schools’ SQL section.
In the next video, Graeme will explore some other means of interacting with the data in our databases.
Microsoft Future Ready: Fundamentals of Big Data
Microsoft Future Ready: Fundamentals of Big Data
Reach your personal and professional goals
Unlock access to hundreds of expert online courses and degrees from top universities and educators to gain accredited qualifications and professional CV-building certificates.
Join over 18 million learners to launch, switch or build upon your career, all at your own pace, across a wide range of topic areas.
