{"id":23465,"date":"2023-08-25T02:19:49","date_gmt":"2023-08-25T02:19:49","guid":{"rendered":"https:\/\/machinelearningplus.com\/?p=23465"},"modified":"2023-09-13T17:41:02","modified_gmt":"2023-09-13T17:41:02","slug":"sql-tutorial","status":"publish","type":"post","link":"https:\/\/machinelearningplus.com\/sql\/sql-tutorial\/","title":{"rendered":"SQL Tutorial – A Simple and Intuitive Guide to the Structured Query Language"},"content":{"rendered":"

SQL, short for Structured Query Language is a programming language used to communicate with databases and do various types of Data wrangling operations. This is an essential skillset for any type of Data related jobs. In this tutorial, let’s get started with the basics of SQL.<\/strong><\/p>\n

1. Why you should learn SQL<\/strong>?<\/h2>\n

First, let’s understand what is SQL.<\/strong><\/p>\n

SQL, pronounced “see-quel” or “S-Q-L”, is a programming language specifically designed for managing databases.<\/p>\n

SQL is used to communicate with databases to retrieve and manipulate data.<\/p>\n

Application or websites use databases to store and access data, like user information, transaction data, product details, etc. So, if you have to create such applications, you will need to know at least basic SQL.<\/p>\n

It is particularly effective for handling structured data, i.e., data incorporating relations among entities and variables.<\/p>\n

The History of SQL<\/h3>\n

SQL was first developed at IBM by Donald D. Chamberlin and Raymond F. Boyce<\/strong> in the early 1970s.<\/p>\n

It was initially called SEQUEL (Structured English Query Language) and was designed to manipulate and retrieve data stored in IBM’s original quasi-relational database management system, System R, which a group at IBM San Jose Research Laboratory had developed.<\/p>\n

Over time, SQL became a standard tool for managing data, and it has been maintained and standardized by the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO)<\/em> since 1986.<\/p>\n

It is still effective and in-practice to this day, even modern databases adopting SQL at it’s core.<\/p>\n

Why Learn SQL?<\/h3>\n

It is the most popular, largely accepted way to query data from large databases and do all sorts of data wrangling. It is a basic knowledge requirement expected out of Data professionals.<\/p>\n

\n SQL and relational databases are everywhere!<\/strong><\/p>\n

Helpful in pursuing a Data Science Career<\/strong><\/p>\n

Almost every data related technology supports SQL in some form<\/strong><\/p>\n

Handle large amounts of structured data<\/strong><\/p>\n

Easy to learn. Get good with practice.<\/strong>\n<\/p><\/blockquote>\n

Learning SQL (Structured Query Language) can be advantageous for a number of reasons:<\/p>\n

1) Data Management<\/strong>: SQL is a standard language for managing data held in a relational database management system (RDBMS) or for stream processing in a relational data stream management system (RDSMS).<\/p>\n

2) In Demand Skill<\/strong>: Makes you more marketable for jobs. From data analysts and data scientists to software engineers and database administrators, many jobs require SQL skills.<\/p>\n

3) Data Analysis<\/strong>: SQL is designed not just to manage data, but to retrieve and analyze it. You can use SQL to perform complex queries that help you understand data trends, patterns, and insights.<\/p>\n

4) Interoperability<\/strong>: Almost every RDBMS (like MySQL, Oracle, SQL Server, SQLite, Postgres) uses SQL, even with some differences in dialect. This means that learning SQL gives you skills that you can use across various database systems.<\/p>\n

5) Integrations with Other Languages<\/strong>: SQL integrates with other programming languages such as Python, Java, and R.<\/p>\n

6) Handling Large Databases<\/strong>: SQL can handle databases of all sizes, from a few hundred entries to millions or even billions. This makes it a valuable for companies dealing with big data.<\/p>\n

7) Long-term Relevance<\/strong>: SQL has been around since the 1970s and is still going strong. The importance of SQL in data management ensures its relevance for many years to come.<\/p>\n

8) Ease of Learning<\/strong>: While SQL has depth and complexity. It has a short learning curve but more you practice the better you get at it.<\/p>\n

SQL is a powerful and robust tool for extracting relevant and useful data from a large dataset.<\/p>\n

While SQL has traditionally been the specialty of highly-trained data analysts and programmers, it’s finding greater acceptance among non-technical folks.<\/p>\n

2. Types of SQL servers<\/strong><\/h2>\n

SQL servers generally refer to a category of software that provides relational database management system (RDBMS) services, which are often used in conjunction with the SQL (Structured Query Language) programming language.<\/p>\n

Here are several types of SQL servers provided by different software vendors:<\/p>\n

    \n
  1. Microsoft SQL Server<\/a><\/strong>: A widely used commercial SQL server from Microsoft.<\/p>\n<\/li>\n
  2. \n

    MySQL<\/a><\/strong>: An open-source RDBMS popular for web applications, now owned by Oracle.<\/p>\n<\/li>\n

  3. \n

    PostgreSQL<\/a><\/strong>: An open-source object-relational database system known for its robustness and complexity.<\/p>\n<\/li>\n

  4. \n

    Oracle Database<\/a><\/strong>: A comprehensive and powerful commercial RDBMS from Oracle Corporation.<\/p>\n<\/li>\n

  5. \n

    MariaDB<\/a><\/strong>: An open-source RDBMS, and a community-developed fork of MySQL.<\/p>\n<\/li>\n

  6. \n

    IBM DB2<\/a><\/strong>: An enterprise-level, high-performance DBMS from IBM.<\/p>\n<\/li>\n

  7. \n

    Amazon RDS<\/a><\/strong>: A scalable and managed RDBMS service from Amazon Web Services.<\/p>\n<\/li>\n

  8. \n

    Google Cloud SQL<\/a><\/strong>: A fully-managed RDBMS service offered by Google Cloud.<\/p>\n<\/li>\n

  9. \n

    Azure SQL Database<\/a><\/strong>: A fully managed RDBMS service provided by Microsoft’s Azure Cloud platform.<\/p>\n<\/li>\n

  10. \n

    SQLite<\/a><\/strong>: A lightweight, disk-based database with an embedded processing model.SQLite is a C library that provides a lightweight disk-based database. It doesn’t have a separate server process like most other SQL servers.<\/p>\n<\/li>\n

  11. \n

    Apache HIVE<\/a><\/strong>: Manage petabytes of data residing in distributed storage using SQL.<\/p>\n<\/li>\n<\/ol>\n

    The sqlite3 module in Python<\/strong> provides an SQL interface for interacting with SQLite databases<\/strong>. This module is an embedded SQL database engine and does not have a separate server process.<\/p>\n

    4. What is a Database and Table?<\/strong><\/h2>\n

    Before you start learning SQL, it is very important to understand what is Database and Table?<\/p>\n

    \"\"<\/a><\/p>\n

    1) Database<\/strong>: Imagine a library. It’s a place where lots of information is stored. This information is organized in a way that you can find and access what you’re looking for.<\/p>\n

    A database is the equivalent of this library in the digital world. It’s a structured set of data. So, a database might be used to store information for a website, a business, a game, and so on. It helps us store, manage, and retrieve that information efficiently.<\/p>\n

    2) Tables<\/strong>: Now within this library, books are organized into different sections or shelves based on categories like ‘Science Fiction’, ‘History’, ‘Biography’ and so forth. Tables are just like these sections in our library analogy.<\/p>\n

    In a database, a table is a set of data elements (values) that is organized using a model of vertical columns (which are identified by their name) and horizontal rows, the cell being the unit where a row and column<\/strong> intersect.<\/p>\n

    A table has a specified number of columns, but can have any number of rows.<\/p>\n

    \"\"<\/a><\/p>\n

    SQL Syntax example<\/h2>\n

    Typically we write SQL Commands in CAPS. However, SQL is NOT case sensitive. So, SELECT<\/code> is same as select<\/code> in SQL.<\/p>\n

    SELECT ProductID, CustomerID, COUNT(*)\nFROM Orders\nWHERE Quantity > 0\nGROUP BY ProductID, CustomerID\nHAVING COUNT(*) > 5\nORDER BY ProductID ASC, CustomerID DESC;\n<\/code><\/pre>\n
    Explanation:<\/p>\n