SQL Syllabus, SQL can be used by Database developers to create & manipulate databases, Database Administrators for Database Administration, and Software Testers to conduct Database Testing.
SQL (Structured Query Language) is a standard language for storing, manipulating, and retrieving data in databases.
SQL was initially developed at IBM in the 1970s, It became a standard of the American National Standards Institute (ANSI) in 1986.
Some common relational database management systems that use SQL are MySQL, MS SQL Server, MS Access, Oracle, Sybase, Informix, Postgres, etc.
Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system. ex: Pl/SQL in Oracle.
Subsets of SQL are Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL).
SQL (Structured Query Language) Syllabus
(What is SQL?, Purpose of SQL, Who should learn SQL?, What are the subsets of SQL?, Data Definition Language, Data Manipulation Language, Data Control Language, and SQL vs. NoSQL)
(What is a Database?, Database Objects, Database Tables, Table Records, Types of Database Management Systems, Relational Database Management Systems, and SQL/Relational Databases vs. No SQL Databases)
(Download MS SQL Server or Oracle or MySQL Database Engine, and Install. Launch SQL Server Management Studio, Select New Query, and launch SQL Query. Type SQL Commands and Execute.)
4. SQL Syntax
(Focus on SQL Syntax, SQL keywords, SQL is not case sensitive, SQL Comments, SQL Commands, and writing SQL Statements.)
5. SQL Data Types
(SQL Numeric data types, Date and Time data types, Character and String data types, Unicode character string data types, Binary data types, and Miscellaneous data types.)
(SQL Arithmetic Operators, Comparison Operators, Logical Operators, and Bitwise Operators)
7. SQL Expressions
(SQL Boolean Expression, SQL Numeric Expression, and SQL Date Expression)
(SQL Comments, Comments are used to explain sections of SQL statements, or to prevent the execution of SQL statements. Single-Line Comments, and Multi-line Comments)
(SQL Data Definition Language Commands, Create, Alter, Drop, Truncate, and Rename.
Data Definition Language Operations, Create a Database, Use Database, Rename a Database, Drop Database, Create a Table, Rename Table, Add a Column to exiting Table, Add multiple columns to existing Table, Modify an existing column, Rename a Column, Drop a Column, Truncate a Table, and Drop a Table.)
(Data Manipulation Language Commands, SELECT, INSERT, UPDATE, and DELETE.
Data Manipulation Language Operations, Retrieving data from a table, Inserting data into a table, Updating existing data into a table, and Deleting all records from a table.)
13. SQL – Data Control Language Commands
DCL includes commands such as GRANT and REVOKE which mainly deal with the rights, permissions, and other controls of the database system.
(Providing the users the access or privileges to the database objects, and Taking back or canceling the privileges or permissions previously allowed or denied to the users.)
SQL has many built-in functions for performing calculations on data. SQL Aggregate Functions, SQL String Functions, SQL Date Functions, and SQL Scalar functions.
16. SQL Queries and Sub Queries
A Query is used to traverse over some data that may be of small or large quantity to find the needed information.
A Subquery is a type of query which is written inside another query. A subquery becomes a part of a larger query. A subquery is also called INNER QUERY OR NESTED QUERY.
17. SQL Clauses
Clauses in SQL are similar to conditionals in high-level languages. We have a large variety in the SQL clauses like the Where clause, Union Clase, Order By clause, etc.
The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.
19. SQL Views
Views in SQL are kind of virtual tables. A view also has rows and columns as they are in a real table in the database. We can create a view by selecting fields from one or more tables present in the database.
20. SQL Indexes
An index is a schema object. It is used by the server to speed up the retrieval of rows by using a pointer. It can reduce disk I/O(input/output) by using a rapid path access method to locate data quickly.
21. SQL Transactions
Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of database program.
22. SQL Injection
SQL injection, also known as SQLI, is a common attack vector that uses malicious SQL code for backend database manipulation to access information that was not intended to be displayed. This information may include any number of items, including sensitive company data, user lists, or private customer details.
There are a lot of databases used today in the industry. Some are SQL databases, some are NoSQL databases. The conventional database is an SQL database system that uses a tabular relational model to represent data and their relationship. The NoSQL database is the newer database that provides a mechanism for storage and retrieval of data other than the model of the tabular relations used in relational databases.
Note: This SQL Syllabus is for Database Developers, Database Administrators, and Database Testers.
Popular Relational Database Management Systems
Oracle database is a relational database management system. It is also called OracleDB, or simply Oracle. It is produced and marketed by Oracle Corporation. It was created in 1977 by Lawrence Ellison and other engineers. It is one of the most popular relational database engines in the IT market for storing, organizing, and retrieving data.
Microsoft Access is a Database Management System (DBMS) from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. It is a part of the Microsoft Office suite of applications, included in the professional and higher editions.
MS SQL Server
Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data. Microsoft markets different editions of Microsoft SQL Server, aimed at different audiences and for workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users.
MySQL is free and open-source software under the terms of the GNU General Public License and is also available under a variety of proprietary licenses. MySQL was owned and sponsored by the Swedish company MySQL AB, which was bought by Sun Microsystems (now Oracle Corporation).
MySQL has stand-alone clients that allow users to interact directly with a MySQL database using SQL, but more often, MySQL is used with other programs to implement applications that need relational database capability.
Sybase, a software company founded in 1984 that specializes in enterprise infrastructure and integration of platforms, databases, and applications. It was originally known for its SQL Server relational DBMS but expanded its line in 1995 when it acquired Powersoft, makers of the PowerBuilder application development software.
Sybase product families include databases, development tools, integration middleware, enterprise portals, and mobile and wireless servers. In 2010, Sybase was acquired by SAP.
Db2 is a family of database management system (DBMS) products from IBM that serve several different operating system (OS) platforms.
IBM DB2/400 V4R5 is an integrated part of OS/400, the AS/400 operating system. Everything in the OS/400 operating system, including the database, is organized as objects. OS/400 contains over 80 types of objects, including programs, database files, and user profiles.