Select Statement  «Prev  Next»

Lesson 7 Setting up your database
Objective Set up the Database of your Choice, Oracle, MySQL, DB2, SQL-Server

Set up Database of your Choice

Try running the scripts yourself, and if you have trouble, give the scripts to your DBA. He or she can apply the scripts quickly and easily, and then you will be able to run your test again. The scripts create the database and tables that will let you query the database and experiment with information without harming any production information that may be on your server. Because the database can be rebuilt at any time, you will not have to worry about deleting everything or otherwise resetting the database. For database-specific directions, follow the link for Microsoft SQL Server and Microsoft Access from the list below. For other databases, consult your administrator or the documentation.

Microsoft Access

Microsoft Access, known lately as Microsoft Office Access, is a desktop relational database (relatively relational). It purports to be an integrated solution combining elements of a relational database engine, application development infrastructure (complete with built-in programming language and programming model), and reporting platform. Unlike other RDBMSs, this is a file-based database and as such has inherent limitations in performance and scalability. For example, while the latest version theoretically allows for up to 255 concurrent users, in practice anything more than a dozen users slows the performance to a crawl. It also supports only a subset of SQL Standard, as well as a number of features available in its own environment only. One of the features is linking in tables from remote databases that allow it to be used as an application front end to any ODBC/OLEDB-compliant database.


PostgreSQL is an object-relational database (ORDBMS) with additional object features, with an emphasis on extensibility and standards compliance. As a database server, its primary function is to store data securely, and to allow for retrieval at the request of other software applications. It can handle workloads ranging from small single-machine applications to large database web applications (or for data warehousing) with many concurrent users. On the Mac PostgreSQL is the default database for web hosting and it is also available for Microsoft Windows and Linux (supplied in most distributions).
PostgreSQL is ACID-compliant and transactional.
PostgreSQL has
  1. updatable views and materialized views,
  2. triggers, and
  3. foreign keys.
In addition this database supports functions and stored procedures. PostgreSQL is developed by the PostgreSQL Global Development Group, a diverse group of many companies and individual contributors. It is free and open-source software, released under the terms of the PostgreSQL License.

SQL Relational Theory

Microsoft SQL Server

SQL Server began as partnership between Sybase, Microsoft, and Ashton-Tate, with the initial idea to adapt existing UNIX-only Sybase SQL Server to then-new IBM operating system OS/2. Ashton-Tate later dropped out of the partnership, and the IBM OS/2 operating system faded into oblivion. Microsoft and Sybase were to share the world. Microsoft was to develop and support SQL Server on Windows and OS/2, and Sybase was to take over UNIX platforms. The partnership formally ended in 1994 and Microsoft SQL Server still used fair chunks of Sybase technology. In 1998, beginning with the release of Microsoft SQL Server 7.0, the last traces of Sybase legacy were eliminated, and a brand new RDBMS set out to conquer the Windows world. As of today, Microsoft holds about 20 percent of the RDBMS market, though on Windows it reigns supreme. The latest version as of this writing is Microsoft SQL Server 2019 .

  1. Microsoft SQL Server
A good site to reference the PUBS database is at Microsoft.