What is SQL? History of SQL
IBM began a major research project in the early 1970s called System/R. The goals of this project were to prove the viability of the relational model and to gain some experience in designing and implementing a relational database. The researchers’ initial endeavors between 1974 and 1975 proved successful, and they managed to produce a minimal prototype of a relational database.
In addition to their efforts to develop a working relational database,
researchers were also working to define a database language. The work performed at this laboratory is arguably the most commercially significant of the initial efforts to define such a language. In 1974, Dr. Donald Chamberlin and his colleagues developed Structured English Query Language (SEQUEL). The language allowed users to query a relational database using clearly defined English-style sentences. Dr. Chamberlin and his staff first implemented this new language in a prototype database called SEQUEL-XRM.
The initial feedback and success of SEQUEL-XRM encouraged
Dr. Chamberlin and his staff to continue their research. They completely revised SEQUEL between 1976 and 1977 and named the new version SEQUEL/2. However, they subsequently had to change the name SEQUEL to SQL (Structured Query Language or SQL Query Language) for legal reasons—someone else had already used the acronym SEQUEL. To this day, many people still pronounce SQL as sequel, although the widely accepted “official” pronunciation is es-cue-el. SQL provided several new features, such as support for multi-table queries and shared data access by multiple users.
Soon after the emergence of SQL, IBM began a new and more ambitious project aimed at producing a prototype database that would further substantiate the feasibility of the relational model. They called the new prototype System R and based it on a large subset of SQL. After much of the initial development work was completed, IBM installed System R in a number of internal sites and selected client sites for testing and evaluation. Many changes were made to System R and SQL based on the experiences and feedback of users at these sites. IBM closed the project in 1979 and concluded that the relational model was indeed a viable database technology with commercial potential.
In 1977, Relational Software, Inc. was formed by a group of engineers in Menlo Park, California, to build a new relational database product based on SQL. They called their product Oracle. Relational Software shipped its product in 1979, beating IBM’s first product to market by two years and providing the first commercially available relational database management system (RDBMS). One of Oracle’s advantages was that it ran on Digital’s VAX minicomputers instead of the more expensive IBM mainframes. Relational Software has since been renamed to Oracle Corporation and is one of the leading vendors of RDBMS software.
Meanwhile, Michael Stonebraker, Eugene Wong, and several other professors at the University of California’s Berkeley computer laboratories were also researching relational database technology. Like the IBM team, they developed a prototype relational database and dubbed their product Ingres. Ingres included a database language called Query Language (QUEL), which, in comparison to SQL, was much more structured but made less use of English-like statements. Ingres was eventually converted to an SQL-based RDBMS when it became clear that SQL was emerging as the standard database language. Several professors left Berkeley in 1980 to form Relational Technology, Inc., and in 1981 they announced the first commercial version of Ingres. Relational Technology has gone through several transformations and is now part of Computer Associates International, Inc. Ingres (now owned and supported by a company called Actian) is still one of the leading database products in the industry today.
Now we come full circle back to IBM. IBM announced its own RDBMS called SQL/Data System (SQL/DS) in 1981 and began shipping it in 1982. In 1983, the company introduced a new version of SQL/DS for the VM/CMS operating system (one of several offered by IBM for their mainframe systems) and announced a new RDBMS product called Database 2 (DB2), which could be used on IBM mainframes using IBM’s mainstream MVS operating system. First shipped in 1985, DB2 has become IBM’s premiere RDBMS, and its technology has been incorporated into the entire IBM product line. By the way, IBM hasn’t changed—it’s still IBM.
During the course of more than 40 years, I’ve seen what began as research for the System R project become a force that impacts almost every level of business today and evolve into a multibillion dollar industry.
SQL Query Examples with Answers
Query : Select first name, last name, street address, city, state, and ZIP Code from the employees table.
|
1 2 3 4 5 6 |
SELECT EmpFirstName, EmpLastName, EmpStreetAddress, EmpCity, EmpState, EmpZipCode FROM Employees |
Query : Select the last name, first name, and phone number of all our employees from the employees table.
|
1 2 3 4 |
SELECT EmpLastName, EmpFirstName, EmpPhoneNumber FROM Employees |
Query : Select all the columns from the subjects table.
|
1 2 3 4 |
SELECT * FROM Subjects |
Query : Select the distinct city values from the bowlers table.
|
1 2 3 4 |
SELECT DISTINCT City FROM Bowlers |
Query : Select category from the classes table and order by category.
|
1 2 3 4 5 |
SELECT Category FROM Classes ORDER BY Category |
Query : Select vendor name and ZIP Code from the vendors table and order by ZIP Code in descending order.
|
1 2 3 4 5 |
SELECT VendName, VendZipCode FROM Vendors ORDER BY VendZipCode DESC |
Query : Select last name, first name, phone number, and employee ID from the employees table and order by last name and first name.
|
1 2 3 4 5 |
SELECT EmpLastName, EmpFirstName, EmpPhoneNumber, EmployeeID FROM Employees ORDER BY EmpLastName, EmpFirstName |
One of the interesting things you can do with the columns in an ORDER BY clause is to specify a different sort order for each column.
In the previous example, you can specify a descending sort for the column containing the last name and an ascending sort for the column containing the first name. Here’s how the SELECT statement looks when you make the appropriate modifications:
|
1 2 3 4 5 |
SELECT EmpLastName, EmpFirstName, EmpPhoneNumber, EmployeeID FROM Employees ORDER BY EmpLastName DESC, EmpFirstName ASC |
Query : Select the distinct state values from the customers table.
|
1 2 3 4 |
SELECT DISTINCT CustState FROM Customers |
Query : Select city and stage name from the entertainers table and order by city and stage name.
|
1 2 3 4 5 |
SELECT EntCity, EntStageName FROM Entertainers ORDER BY EntCity ASC, EntStageName ASC |
Getting More Than Simple Columns
Query : Select the vendor name and vendor ID from the vendors table.
This example clearly shows that you can easily use more than one concatenation expression in a SELECT clause to enhance the readability of the information in the result set.
Remember that you can also concatenate values with different data types by using the CAST function.
|
1 2 3 4 |
SELECT 'The ID Number for ' || VendName || ' is ' || CAST(VendorID AS CHARACTER) FROM Vendors |
Now I’ll modify the SELECT statement in the previous example and supply a name for the concatenation expression:
|
1 2 3 4 |
SELECT EmpFirstName || ' ' || EmpLastName AS EmployeeName, EmpPhoneNumber, EmpCity FROM Employees |
Query : Select first name and last name as employee name and DOB as date of birth from the employees table.
|
1 2 3 4 |
SELECT EmpFirstName || ' ' || EmpLastName AS EmployeeName, DOB AS DateOfBirth FROM Employees |
Query : Select first name and last name as agent name and salary plus 50000 times commission rate as projected income from the agents table.
|
1 2 3 4 |
SELECT AgtFirstName || ' ' || AgtLastName AS AgentName, Salary + (50000 * CommissionRate) AS ProjectedIncome FROM Agents |
Query : Select the order number and ship date minus order date as days to ship from the orders table.
|
1 2 3 4 |
SELECT OrderNumber, CAST(ShipDate – OrderDate AS INTEGER) AS DaysToShip FROM Orders |
Query : Select the start time and start time plus 10 minutes as new start time from the classes table.
|
1 2 3 4 |
SELECT StartTime, StartTime + '00:10' AS NewStartTime FROM Classes |
Query : Select the product name, retail price times * quantity on hand as InventoryValue from the products table.
|
1 2 3 4 |
SELECT ProductName, RetailPrice * QuantityOnHand AS InventoryValue FROM Products |
Query : Select the order number, order date, ship date, ship date minus – order date as DaysElapsed from the orders table.
|
1 2 3 4 5 |
SELECT OrderNumber, OrderDate, ShipDate, CAST(ShipDate – OrderDate AS INTEGER) AS DaysElapsed FROM Orders |
Query : Select the engagement number, end date minus – start date plus one + 1 as DueToRun from the engagements table.
|
1 2 3 4 5 6 |
SELECT EngagementNumber, CAST(CAST(EndDate – StartDate AS INTEGER) + 1 AS CHARACTER) || ' day(s)' AS DueToRun FROM Engagements |
Query : Select the engagement number, contract price, contract price times * 0.12 as OurFee, contract price minus – (contract price times * 0.12) as NetAmount from the engagements table.
|
1 2 3 4 5 |
SELECT EngagementNumber, ContractPrice, (ContractPrice * 0.12 AS OurFee, ContractPrice −(ContractPrice * 0.12)) AS NetAmount FROM Engagements |
Query : Select last name || ‘, ’ || and first name concatenated with a comma as Staff, date hired, and ((‘2017-10-01’ minus – date hired) divided by / 365) as YearsWithSchool from the staff table and sort order by last name and first name.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT StfLastName || ', ' || StfFirstName AS Staff, DateHired, CAST(CAST('2017-10-01' − DateHired AS INTEGER) / 365 AS INTEGER) AS YearsWithSchool FROM Staff ORDER BY StfLastName, StfFirstName |
Query : Select the last name || ‘, ’ || and first name as StaffMember, salary, and salary times * 0.07 as Bonus from the staff table.
|
1 2 3 4 |
SELECT StfLastName || ', ' || StfFirstName AS Staff, Salary, Salary * 0.07 AS Bonus FROM Staff |
Query : Select first name || ‘ ’ || and last name as FullName, BowlerAddress, city || ‘, ’ || state || ‘ ’ || and ZIP Code as CityStateZip, BowlerZip from the bowlers table and order by ZIP Code.
|
1 2 3 4 5 6 7 8 9 |
SELECT BowlerFirstName || ' ' || BowlerLastName AS FullName, Bowlers.BowlerAddress, BowlerCity || ', ' || BowlerState || ' ' || BowlerZip AS CityStateZip, BowlerZip FROM Bowlers ORDER BY BowlerZip |
Query : Select bowler ID, match ID, game number, handicap score, raw score, handicap score minus – raw score as PointDifference from the bowler scores table and order by bowler ID, match ID, game number.
|
1 2 3 4 5 6 |
SELECT BowlerID, MatchID, GameNumber, HandiCapScore, RawScore, (HandiCapScore − RawScore) AS PointDifference FROM Bowler_Scores ORDER BY BowlerID, MatchID, GameNumber |
Filtering Data
In this part, I’ll show you how to fine-tune what you retrieve by filtering the information using a WHERE clause.
Query : Select first name and last name from the customers table for those customers who live in where state is equal to = ‘WA’ Washington State.
|
1 2 3 4 5 |
SELECT CustFirstName, CustLastName FROM Customers WHERE CustState = 'WA' |
Query : Select first name and last name from the agents table for all agents hired on March 14, 1977.
|
1 2 3 4 5 |
SELECT AgtFirstName, AgtLastName FROM Agents WHERE DateHired = '1977-03-14' |
Query : Select vendor name and phone number from the vendors table for all vendors except those based in ‘Bellevue’.
|
1 2 3 4 5 |
SELECT VendName, VendPhone FROM Vendors WHERE VendCity <> 'Bellevue' |
Query : Select order number from the orders table where the ship date is earlier than the order date.
|
1 2 3 4 5 |
SELECT OrderNumber FROM Orders WHERE ShipDate < OrderDate |
Query : Select class ID from the classes table for all classes that earn more than four credits.
|
1 2 3 4 5 |
SELECT ClassID FROM Classes WHERE Credits > 41 |
