Comptia Sql

One-tier

A query allows a user to extract only the data needed from a database by selecting specific rows and columns from a table.

Row The rows in a relational database are also called records. The term "record" has a logical meaning: each patient has a record. It is also a term that was originally used for a row in a flat file, and the term continued to be used after the advent of database technology.

A table contains rows of data, which are also called records. For example, in a table containing data about a classroom, there would be one row/record in the table for each student.

A field is an individual piece of data in a record. For example, a patient's last name would be a field in the patient record in a hospital database.

A column can also be called a field

CREATE DATABASE CREATE TABLE INSERT INTO In the database creation process, the database must be created first. It's the all-around container for all of the tables and data.

Data records that are already stored in a digital format can be loaded directly into a database. The database expects to receive data in a digital format. Importing data is the process of copying existing digital data that is already stored on another device and pasting it into the database.

Data in memory is lost once the processing is completed. Data in a database is stored persistently and securely so that it can be used again in the future.

Persistent data is data that is stored permanently in a database. A copy of the data may be loaded into memory at processing time and lost when processing is complete, but the original data is safely stored in the database. All data stored in the database is persistent, whether or not it is accessed frequently.

A flat file system doesn't support multiple concurrent users. A successful online shopping application would be highly likely to have hundreds or thousands of users hitting the database at any given time. A flat file system is not able to support multiple concurrent users. Rather, it only provides access to one user at a time.

The structure of a flat file system is simpler than a database structure, since it has only rows and columns—much like a basic spreadsheet. Flat file systems are still supported by most programming languages and continue to have many relevant uses, such as storing simple collections of data and exporting and importing data between systems.

A database can enforce data types for each column in a table, and can validate data as it is entered into those columns.

A database does enforce data types for each field/column in a table; therefore, it can validate that the correct type of data is being entered and stored in each field.

Structured Query Language (SQL)

Structured Query Language

Relational Database Management Systems A relational database is created and managed by an RDBMS. A database server is the computer on which an RDBMS is installed and running, as it is used to create and manage databases. A query/report builder may be a feature of an RDBMS—or it may be an additional application software package that can connect to a database managed by the RDBMS.

Determine how the database will be used by clients, where it will be hosted, and what information it will store.

A table always has the same number of columns as it has rows.

A query can be used to extract only the data based on specified criteria, while a report can format and summarize the data making it easier to read and interpret.

A database is a collection of related data organized in a structured manner for easier access.

Scalability

There are twelve products.

The order in which they were entered into the database Each time a row is inserted into the database, it is added to the end of the appropriate table. Consequently, rows are stored in the order in which they were loaded into the table.

Database processing speeds are much faster than flat file speeds. A database is organized by relating data together, so its processing speed is much faster than it can be with a flat file. The data stored in a database always processes more quickly because only the parts of the data that are needed are loaded into memory.

Flat file processing speeds are slower than database processing because each entire row must be read into memory whether or not all of the fields in the row are needed.

Multiple users can view the same record at the same time. If more than one user attempts to update the same record at the same time, the RDBMS locks that record so that only one user at a time can update.

Often, many users will be viewing the same record—a product in an online catalog, for example—at exactly the same time. But, as soon as a user begins the order process, that record is locked from access by other users. The RDBMS will actually provide a message to let the second user know that the record is locked because someone else is in the middle of an update.

RDBMS

all examples of Relational Database Management Systems (RDBMS).

Specifying criteria to match values in one or more fields

A query selects and retrieves the correct data, whereas a report formats and summarizes the output.

Comma Separated Values (CSV)

150

Constraints

XML makes it easier to parse and sort through the data in a variety of types of documents. Since Extensible Markup Language (XML) provides an easy way to identify individual pieces of data in different types of documents, it's a good choice for a document database where all the documents may be in different formats—spreadsheets, text files, Word files, etc. XML allows each document to be different and still have identifiable data inside.

XML and HTML are both markup languages, but have different purposes. HTML is for web page development. XML is for data storage.

CustomerNumber The primary key must be able to uniquely identify each customer from all other customers. In past decades, social security numbers were used for that purpose. Now, a good database designer would create a CustomerNumber field to use as the primary key and generate unique numbers for each customer.

A table is made up of rows and columns, where all of the rows include the same columns.

A table contains rows and columns of data, much like a spreadsheet. The columns are the same for all rows. For example, if a "Customer" table includes columns like customerID, lastName, and firstName, then each row in this table includes these columns. A relational table is very structured with specific fields/columns; consequently, fields are never randomly placed. All rows in a relational table include the same columns. There are no rows with a unique collection of columns. All columns in a relational table apply to the same rows. There are no columns with a unique collection of rows.

The Project table has a primary key of ProjectID. The Employee table has a primary key of EmployeeID and a foreign key of ProjectID. Each of these tables needs a primary key, to uniquely identify each row with the corresponding ID field: ProjectID for the Project table and EmployeeID for the Employee table. Since each employee is assigned to only one project, the Employee table needs the ProjectID field as well, to identify which project each employee is assigned to. This field becomes the foreign key that relates with the Project table, and each ProjectID can appear in several records of the Employee table.

It allows a variable number of characters to be stored for a particular column in each row.

The varchar data type provides for a variable-sized field type. If a book title is twenty-seven characters long, twenty-seven bytes are stored. If a title is only three characters long, only three bytes are stored, resulting in a significant savings in storage space. Varchar specifies a maximum number of bytes per column as a limit, but stores only the number of bytes used.

A relational database

Microsoft SQL Server

It describes the structure of a database.

In a relational database, all attributes of a record are defined up front, and each record has all of the same attributes as the other records. In a key/value pair database, objects and their properties are stored, but each object doesn't have to have all of the properties.

In a key/value pair database, objects and their properties are stored, but each object doesn't have to have all of the properties.

In a relational database, all attributes of a record are defined up front, and each record has all of the same attributes as the other records.

A column with naturally occurring unique values is a good choice to be the primary key. The column, or combination of columns, that becomes the primary key for the table, must be unique. If no such column exists naturally (i.e., social security number or employee id) then an automatically generated sequentially numbered column can be added to the table.

Columns can be in any order in a table. Some columns may be grouped together to make up a logical sequence (i.e., Last Name, Middle Initial, and First Name, or Address, City, State, and Zip Code), but even a random order of columns will work the same way. Each value for a given column has the same data type. The specifications of the table assign a data type to each column (i.e., string, number, date, etc.)

The size of each row affects the amount of storage used, and how quickly it can be processed. Storage is important, but processing speed is often a critical factor in database applications. Rows must be kept as succinct as possible by carefully choosing field sizes, because each row is read into memory during processing.

A column

The structure of the database

The primary key is in one table and it is matched to the foreign key in a secondary table.

There are certain types of data (PowerPoint presentations, text files, Word documents, etc.) that are more easily stored without the rigid formatting a structured database requires.

It is more difficult to store documents and files in fields in a database table. Unstructured and semi-structured databases are made specifically to handle that kind of data.

SQL

To delete a table or an entire database It is a DDL command, which is very powerful in that it can delete an entire table or entire database, which includes all of the data as well

Command used to delete one or more rows of data from an existing table

An individual column can be deleted only by using the ALTER command

ALTER TABLE

Data Definition Language

Command that can be used to add, remove, and change table columns, primary keys, and foreign keys

not a DDL command because it modifies data values inside a table, but doesn't modify the structure of a table

Is a DDL command used to create a new table, and to establish a primary key at creation time. It cannot, however, be used to modify the primary key after it has been created.

INSERT command only inserts data into an existing table and is not a DDL command

All of the records from the "Customer" table were deleted. Without a WHERE clause to specify which rows should be deleted, all of the rows would be deleted. Adding a clause lik e WHERE state = MN" would cause only the rows with Minnesota customers to be deleted.

CREATE

A new database, table, or index is added with the CREATE command

adds a new database

adds an index to the tables, by specifying one or more columns which will be used for faster searching and sorting.

It adds a new row to a table in a database

Database dump

Term used for a copy of the database or table schema along with the data records--all expressed as SQL CREATE and INSERT statements. The dump is used to create an exact duplicate of the database structure and load an exact copy of the data from the original database.

One-tier

When the application, which includes reports, forms, queries, etc., is hosted on the same computer as the actual database, it's called a standalone or one-tier database application architecture

Storing the database and the application on two separate servers makes for a two-tier architecture

Used to describe an even more complex structure where parts of the application may reside on different servers.

The list of product names and prices will now be sorted by price.

query that causes the query output to be sorted in ascending order.

A database dump includes both the database data and the structure, so it could be restored whether the offending error was in the data or in the structure.

A database can become corrupted in a variety of ways. A user can enter bad data that corrupts a table, a hacker can introduce a virus that changes permissions, or a program can have a bug that alters the wrong table. Either way, the error could be in the data or the structure, and both are covered by the database dump.

A database can contain mission-critical data, which an organization may not be able to afford to lose.

Foreign key A relationship between two tables requires a common column between the two tables. A foreign key is added to a table for the purpose of being that common column that matches back to a primary key in another table.

Added to a table for the purpose of making sure that each row in the table can be uniquely identified

An index on the table is created for the purpose of making sorting and searching easier and faster.

A feature that helps users by reducing the possibility of errors during data entry.

Log into phpMyAdmin and run SQL statements at the command prompt.

It is a data definition language statement.

Standalone

DELETE deletes rows from a table DROP eliminates the entire table

The database runs on a database server, while the application is run on the client computer.

Power users and programmers

Query and report builders are typically used by those who need to interact closely with a database, but might not want to learn SQL commands. However, programmers also find them useful for quickly extracting data in some situations.

User interface/utility An ATM machine is an excellent example of a user interface that allows users to access a database. For example, withdrawing $100 from a savings account via an ATM updates the database to decrease your account balance by $100.

The owner of a table can assign only the permission to view (not update) data in the table to other users.

It depends on the user's level of SQL experience, overall purpose, and access permissions.

ALTER TABLE

In the SELECT statement, the column name is prefixed by the table name.

The owner of the "Customer" table

To change the value in one or more fields in a table The UPDATE command specifies one or more field values to be updated. If a WHERE clause is added, those fields are updated only for the rows that satisfy the selection criteria. For example, the command could be used to change the grade for one specific student from a B to a C if the studentID was specified in a WHERE clause.

It comprises SQL commands that can add to or modify the structure of a database.

Both display data, but a form can be used to update the contents of the database as well.