BUS611: Data Management

5a. Describe SQL and summarize its basic operators

• What is SQL, and what are some of its most common uses in managing databases?
• What are some standard operators of the SQL SELECT command, and how are they used?

A common way users and designers interact with relational databases is to use Structured Query Language, SQL. SQL is designed to be an easy-to-use language with a natural language feel. This makes it well-suited to work with relational databases.
 
SQL is a communication programming language that has been around for decades. It is the standard language for relational database management systems. Organizations cannot have an effective data management program without SQL. SQL is a universal and standard language that interfaces with relational databases. SQL is used to communicate, analyze, and provide results from the data fields stored in a relational database.
 
A query is a question or inquiry to a data set within a database, commonly referred to as a database query. SQL queries require a SELECT statement informing the system to look for specific data. Therefore, SQL query statements retrieve records asked from a database table. The SELECT statement is probably the most commonly used SQL statement employed by end users of database systems and is worthy of your careful attention. The SELECT statement will include operators. An operator manipulates individual data items and returns a result. SQL is an industry-standard language that consists of user-defined and system-defined functions and queries.
 
In a DBMS, common operations like creating tables and their structures can be implemented with SQL. The language can also add, modify, and delete data from the database structure. SQL can also be used to run extractions and queries to facilitate the transformation of raw data into formatted information for use in decision support applications.
 
The American National Standards Institute (ANSI) adopted the official SQL standard in 1986. Then, the International Organization for Standardization (ISO) adopted SQL IN 1987. SQL is a standardized programming language used to perform various operations that manage relational database management systems (RDBMS). For example, if you are asked to identify and calculate from a table of customers whose last name is "Jones'', SQL is a programming language used to communicate this command or query to the database.
 
There are many advantages to learning SQL. SQL is a universal language and intersects almost every industry. SQL is open-source and easier to learn compared to other programming languages. Open-source is code that is made freely available for possible modification and redistribution. You can manage millions of rows of data using SQL. Remember, RDBMS are designed to store millions of rows of data, and SQL is the language that lets you manage enormous amounts of data.
 
This is the syntax of a typical SQL command:
 

CREATE TABLE <tablename>
(
ColumnName, Datatype, Optional Column Constraint,
ColumnName, Datatype, Optional Column Constraint,
Optional table Constraints
);
 
SELECT FirstName, LastName, phone
FROM Employees
ORDER BY LastName

To review, see Data Manipulation Language.

5b. Use relational views to simplify database processing

• What are database views, and how can they be used?
• What is the difference between a dynamic view and a static view?

A database can also be divided into subsets called views. These views can then be used to run specialized queries that affect the data in the view. This makes for more efficient execution of queries. Views can also be saved for later use. A database view is a searchable object in a database that is defined by a query. Though a view doesn't store data, you can query a view like a table. Views allow queries to be simplified for developers. Views are also useful for security. In larger organizations, where many developers may be working on a project, views allow developers to access only the data they need. What they don't need, even if it is in the same table, is hidden from them, safe from being seen or manipulated.
 
Database views can be either static or dynamic. A dynamic view draws data from one or more tables and would include all the columns in those tables. Dynamic views are updated automatically if related or extended objects are created or changed. A static view draws data from multiple tables using the SELECT command and WHERE clauses. The WHERE clause filters records based on specified criteria. Static views must be manually updated when related or extended objects are created or changed.
 
 

5c. Use data definition language (DDL) commands to create tables

• How is SQL used to create the database and tables?
• How is SQL used as a data definition language?

SQL can be used to create the database and table structures, using SQL as a data definition language (DDL). A DDL is simply any tool we use to create the database structure in a database and manipulate data, for example inserting data, manipulating data, querying data, and so on. Most commercial relational database management systems allow for SQL to be used as the data definition language. Common DDL statements include:
 

• CREATE (generates a new table)
• ALTER (changes a table)
• DROP (removes a table from the database)

An example of using SQL as a data definition language would be creating a new database named NDB. The SQL statement would be CREATE DATABASE NDB. After creating this manner, we can use SQL to create the database tables. We can use the ALTER TABLE statement to add or drop any required constraints. The SQL command DROP TABLE can be used as needed to remove tables.
 
 

Unit 5 Vocabulary

This vocabulary list includes the terms that you will need to know to successfully complete the final exam.
 

• ALTER
• CREATE
• database query
• DROP
• data definition language (DDL)
• dynamic view
• open-source
• operator
• SELECT
• static view
• WHERE