SQL and Computer Hardware

Required Resources:

Outline:

  1. SQL basics
  2. Computer Memory & Storage
  3. Computer Ports

SQL (Structured Query Language) Basics:

SQL is the most popular computer language used to create, modify and retrieve data from relational database management systems. It is an ANSI/ISO standard. It is a standardized way of representing a query in a statement or command, as opposed to the tabular forms used in MS Access.

Note: It is possible to view (and edit/compose) queries in MS Access using SQL directly. Simply open any query and select "SQL Query" from the "View" menu. However, the precise SQL notation used by Access is slightly different than the SQL terms and syntax used here (and used in most database programming).

The list of commands provided here is just a brief review of the detail provided in the SQL tutorial readings.

  1. Select and From are used to choose a particular field or set of fields from a database containing one or more tables.
    1. Select queries are formulated as follows: "Select Field From Table"
    2. Using the following formulation "Select * From Table" will select all the fields from a given table.
    3. When working with multiple tables, use: Table.Field notation.

  2. Where is used in conjunction with Select and From to specify conditions under which fields or records are selected. These conditions are specified using
    = Equal
    <> or != Not Equal
    < Less Than (use only with numerical datatypes)
    > Greater Than (use only with numerical datatypes)
    <= Less Than or Equal To (use only with numerical datatypes)
    >= Greater Than or Equal To (use only with numerical datatypes)
    IN Allows the user to supply a list of values as conditions in a query. (Could be either numeric or textual, but are generally textual)
    BETWEEN Allows the user to select a range of values as a query condition. (Tends to be numeric.)
    LIKE Very useful for searches, in "free text" fields. Used in conjunction with the % sign, which can stand in for any character or set of characters. (The % sign is also known as a "wildcard" character. "Like is used only with textual datatypes)

  3. Joins: a way of using primary and foreign keys to carry out Select queries across two or more tables. "Join" is not a particular command in SQL, but rather, a way of bringing tables together in queries that follow the "relationships" discussed in the materials on databases provided earlier.
  4. Sub-queries: a way of further qualifying or filtering results of an existing query. Generally one "Select" query is inserted into another query using parentheses ( ), and the "IN" condition.

    SELECT first_name, last_name
    FROM employees
    WHERE department_id = (SELECT department_id
    FROM department
    WHERE section_id = 1112)

SQL Practice: Voluntary, Ungraded Exercise

To see SQL in action, and to understand how a few SQL queries types work on an actual database, go through the steps outlined at: http://learningspaces.org/1311/sql/

These steps will likely take 10-20 minutes. Feel free to try them more than once.

Computer Memory and Storage

Memory - generally used for faster, and temporary forms of storage

 

RAM (Random Access Memory): used to store things such as programs and data temporarily while the computer is using them. It can be accessed at very high speeds.
 

 

RAM Cache: often built into the microprocessor, hard drives, or other devices. It represents a small amount of very high speed dedicated memory used so that important parts of a computer can work at full speed without having to constantly request information every time from slower devices or the rest of the system.
 

 

Flash memory is used for easy and fast information storage in such devices as digital cameras and home video game consoles. It is used more as a hard drive than as RAM. Access to flash memory is slower than access to memory cache or RAM integrated into the computer system. Flash memory generally does not require power to retain data.

Flash memory is also used in "USB keys" or "drives." These are used much as floppy discs were used earlier: as a portable and inexpensive means of sharing files between computers. They plug into the USB port of a computer, and currently have capacities of up to one GB (1000Mb).

Storage -sequential memory devices such as magnetic tapes, discs and drums, in which the mechanical movement of the storage medium forces the computer to access data in a fixed order.
 

Hard Disk (Drive):

Medium for permanent storage of data. Magnetic platters, electronics and mechanics make up a hard disk. The platters are fixed to a spindle. On each side of a platter there is a read/write head, attached to the end of a boom. Each platter is divided in to tracks, which again is divided into sectors. Data is stored and retrieved sector-by-sector. After data files are written and re-written, they becomes fragmented or scattered across the surface of the patters. This is ameliorated through a process known as "defragmenting" the hard drive.

Hard disk drives are currently reaching capacities of one terabyte (1000 GB; 1,000,000 Mb) . Most sold now, however, are in the 40 - 200 GB range.

 
 

CD-ROMs:

An abbreviation for "Compact Disc Read-Only Memory"; a non-volatile optical data storage medium using the same physical format as audio compact discs, readable by a computer with a CD-ROM drive. A CD-ROM is a flat, metallized plastic disc with digital information encoded on it in a spiral from the center to the outside edge. The CD-ROM Yellow Book standard was established in 1985 by Sony and Philips.
 
 

DVD-ROMs: is an optical disc storage media format that can be used for storing data, including movies with high video and sound quality. DVD's resemble compact discs: their physical dimensions are the same—12cm or the mini 8cm—but they are encoded in a different format and at a much higher density. Unlike CDs, all DVD's must contain a file system. This file system is called UDF, and is an extension of the ISO 9660 Standard used for Data-CDs. DVD's are made from a 0.6 mm thick disc of polycarbonate plastic coated with a much thinner (reflective) aluminum layer. Two such discs are glued together to form a 1.2 mm double-sided disc.

 
 

DVD-RAM:

Used in computers as well as camcorders and personal video recorders since 1998. Compared with other DVD's, DVD-RAM is more closely related to hard disk technology, as it has concentric tracks instead of one long spiral track.

Computer Ports (adapted from Circuit City documentation): An interface port is a socket on the outside of the PC's case that allows you to connect external devices such as keyboards, mice, printers, scanners, digital cameras and joysticks.

Keyboard/mouse port: Many systems have ports for plugging in a keyboard and mouse or other device. These special-purpose ports are gradually being replaced by general-purpose USB ports.
USB:Most PCs come with at least one USB port. USB ports are the most popular type of port, and allow for high-speed communication (up to 12Mbps) between external devices and your computer. Printers, scanners, digital cameras, memory sticks (or USB Keys) and many other peripherals use this port. USB-compatible devices may be connected to your PC through the USB port on the back of the computer or through a USB hub.
USB 2.0 boasts a 480Mbps data transfer rate. USB 2.0, also called High-Speed USB, is compatible with CD and DVD drives, USB 2.0 features total backward compatibility with the older interface. The 2.0 interface requires a PCI adapter card for PCs, and a PCMIA card for notebook computers to allow for the massive amount of data transfer.
IEEE1394
It goes by several different names; Sony, for example, calls it i.Link™, and Apple calls it FireWire®. IEEE1394 ports and cables are designed to transmit images and video in a purely digital format between your system and other devices, such as a digital camcorders and cameras. This interface runs slightly slower than USB 2.0 at 400Mbps, but is essential for anyone interested in digital imaging applications.

Developed 2005 by Dr. Norm Friesen