MODULE 1: what the hell is sql?

Introduction

Aim of the course

After the completion of this course, you will be able to function as a SQL Developer.  You will be able to use Microsoft SQL Server Management Studio (and other RDBMS) to build a database solution, construct queries to enter, edit and obtain data stored in the database to provide analytical insight about the data.

Relational Databases

A relational database is a database model that stores data in tables. The vast majority of databases used in modern applications are relational, so the terms "database" and "relational database" are often used synonymously. Likewise, most database management systems (DBMS) are relational database management systems (RDBMS). Other database models include flat file, hierarchical and NoSQL databases.

Each table in a relational database contains rows (records) and columns (fields). In computer science terminology, rows are sometimes called "tuples," columns may be referred to as "attributes," and the tables themselves may be called "relations." A table can be visualized as a matrix of rows and columns, where each intersection of a row and column contains a specific value. It is "relational" since all records share the same fields.

Database tables often include a primary key, which provides a unique identifier for each row within the table. The key may be assigned to a column (which requires a unique value for each row), or it may be comprised of multiple columns that together form a unique combination of values. Either way, a primary key provides an efficient way of indexing data and can be used to share values between tables within a database. For example, the value of a primary key from one table can be assigned to a field in a row of another table. Values imported from other tables are called foreign keys.

The standard way to access data from a relational database is through an SQL(Structured Query Language) query. SQL queries can be used to create, modify, and delete tables, as well as select, insert, and delete data from existing tables.

SQL

SQL, is an acronym for Structured Query Language and can be pronounced as either "sequel" or "S-Q-L."  It is a query language used for accessing and modifying information in a database.  The language was first created by IBM in 1975 and was called SEQUEL for "Structured English Query Language." Since then, it has undergone a number of changes.  SQL communicates with databases via a graphical user interface like the one you will install (SQL Server Management Studio) in the Installation Guide.  Other popular database management systems include SQLite, Oracle, PostgreSQL, Microsoft Access, DB2 and Sybase.  They all have their own graphical user interface.

Microsoft SQL Server

Microsoft SQL Server is built on top of SQL, a standardized programming language that database administrators (DBAs), SQL Developers, Business Analysts, Marketing Analyst, Data Scientist and many other professionals use to manage databases and query the data they contain. SQL Server is tied to Transact-SQL (T-SQL), an implementation of SQL from Microsoft that adds a set of proprietary programming extensions to the standard language.

Microsoft has released 11 versions of SQL Server between 1995 and 2017. SQL Server is now an enterprise-class relational DBMS that competes with Oracle Database, DB2 and other rival platforms for high-end database uses. Over the years, Microsoft has also incorporated various data management and data analytics tools into SQL Server, as well as functionality to support new technologies that has emerged, including the web, cloud computing and mobile devices.

Microsoft SQL Server 2017, became available in March 2017, it has been developed as part of a "mobile first, cloud first" technology strategy.  SQL Server 2016 & 2017 added new features like automatic database tuning, graph database capabilities, real-time operational analytics, data visualization and reporting on mobile devices, in addition to hybrid cloud support that lets DBAs run databases on a combination of on-premises systems and public cloud services to reduce IT costs. For example, a SQL Server Stretch Database technology moves infrequently accessed data from on-premises storage devices to the Microsoft Azure cloud, while keeping the data available for querying, if needed.

For more than 20 years Microsoft SQL Server ran exclusively on Windows. Beginning with SQL Server 2016, Microsoft has made the DBMS available on Linux.  The support for running SQL Server on Linux moved it onto an open source operating system commonly found in enterprises, giving Microsoft potential inroads with customers that don't use Windows or have mixed server environments.

General hints

Questions or exercises are written as:

0.) Have you successfully installed SQL Server 2017 SP1 Express edition?

You are strongly encouraged not only to answer the questions but also to try out all presented commands with some examples of your own.