Database Management/SQL

From Wikiversity
Jump to navigation Jump to search

Objectives and Skills[edit | edit source]

This lesson assists users in creating SQL databases and tables using Microsoft SQL Server/Express.

Readings[edit | edit source]

  1. McFadyen: Relational Databases and Microsoft Access - Chapter 9 (Data Definitional Language (DDL)]
  2. Watt: Database Design - Chapter 15 (SQL)
  3. Watt: Database Design - Chapter 16 (SQL Data Manipulation Language)
  4. Wikipedia: Data Definition Language
  5. Wikibooks: Structured Query Language/Data Manipulation Language
  6. Learning SQL Server/SSMS
  7. W3Schools.com: SQL Views
  8. Create a Database- SQL Server
  9. DML Commands
  10. Microsoft: DML statements

Multimedia[edit | edit source]

  1. Youtube: Learn how to install SQL Server Express and SQL Server Management Studio SQL Server Express: How to install Microsoft SQL Server Express 2017 | SQL Server Tutorial
  2. Youtube: Database Design SQL Server MGMT GUI Tools
  3. Youtube: Overview of SQL Server Basics
  4. Youtube: How To Create Database in SQL Server 2016
  5. Youtube: Introduction to SQL Server - Creating Views
  6. Youtube: SQL Server Views
  7. Youtube: SQL: Views
  8. Youtube: How to find your SQL Server Name for Management Studio
  9. Youtube: How to create primary key and foreign keys using SQL Server
  10. Youtube: How to create primary key fields and ER diagrams
  11. Youtube: Creating Relationships and ER Diagram
  12. Youtube: Creating a Database with Tables and Relationships (11:10)
  13. Youtube: SQL Server Queries/Part 1: (Writing Basic Queries)
  14. Youtube: SQL Server Queries/Part 2 (ORDER BY)
  15. Youtube: SQL Server Queries/Part 3 (WHERE)
  16. Youtube: SQL Server Queries/Part 4 (Calculated Columns)
  17. Youtube: SQL Server Queries/Part 5 (CASE Statements)
  18. Youtube: SQL Server Queries/Part 6 (JOINS (Inner/Outer))
  19. Youtube: SQL Server Queries/Part 7 (Using Functions in Queries)
  20. Youtube: SQL Server Queries/Part 8 (Text Calculations)
  21. Youtube: SQL Server Queries/Part 9 (Date Calculations)
  22. Youtube: SQL Server Queries/Part 10 (Group By and Having)
  23. Youtube: (Microsoft SQL Server Tips/Overview)
  24. Youtube: SQL Server introduction
  25. Youtube: SQL Server Management Studio Intro
  26. Student Recommendation Youtube: Introduction to SQL Server 2008 Create View
  27. Youtube: - How To CREATE VIEW SQL Server Management Studio 2016
  28. Youtube: Importing and Exporting databases SQL Server Management Studio
  29. Youtube: - SQL Basics for Beginners
  30. Youtube: - SQL INNER JOIN Explained
  31. YouTube:UNION and UNION All [2]
  32. YouTube:SELECT DISTINCT Statement
  33. YouTube:How to Restore Master database in SQL Server [3]

Activities[edit | edit source]

  1. Learn How to Install SQL Server 2017 Express and SQL Server Management Studio. YouTube: How to install Microsoft SQL Server Express 2017 | SQL Server Tutorial
  2. Install Microsoft SQL Server 2017 Express (free, specialized edition) Microsoft: SQL Server 2017 Express.
  3. Install Microsoft SQL Server Management Studio (free) Microsoft: SQL Server Studio Management 17.9
  4. Download Northwind and Pubs Sample SQL databases Microsoft: Northwind and Pubs SQL databases
  5. Creating a New Microsoft SQL Server Express database and tables [Tutorial: Microsoft SQL Server Management Studio Express]
  6. Microsoft: SQL Server Generating Scripts Wizard
  7. McFadyen: Relational Databases and Microsoft Access - Chapter 9 (Data Definitional Language (DDL)]. Complete the exercises in Chapter 9.
  8. Watt: Database Design - Chapter 15 (SQL). Complete the exercises in Chapter 15.
  9. (SQL Structured Query Language) SQL (as abbreviation for Structured Query Language) is a domain-specific language, that is applicable beyond a specific proprietory or OpenSource product of relational data stream management systems (RDSMS).
  • (SQL as Programming Language) Explain why it is helpful to define a programming language, that is designed for managing data stored in a RDBMS. SQL defines handling of structured data independet from the specific RDBMS product. What are the similarities according to the usage of SQL in different products technically?
  • (SQL in different Relation Database Management Systems) Compare SQL Server 2017 Express with other (e.g. Open Source) Databases Management Systems like Firebird or MariaDB.
  • (Document Generation based on SQL Queries) Check out the interface, how programmers can access data with SQL statements e.g with LibreOffice-Base by using SQL-Statements. SQL defines the access to data with relations among entities and variables for presenting results of the query in tables of text document that can also be used, e.g. to presented results of the query in LibreOffice Writer! Explain the generic principles of using SQL beyond a specific product!

Lesson Summary[edit | edit source]

  • Microsoft SQL Server Express is a version of Microsoft's SQL Server relational database management system that is free to download, distribute and use.[1]
  • SQL Server Express includes several GUI tools for database management including SQL Server Management Studio. [2]
  • SQL Server Management Studio (SSMS) is a software application used for configuring, managing, and administering all components within Microsoft SQL Server. The tool includes both script editors and graphical tools which work with objects and features of the server.[3]

Key Terms[edit | edit source]

Constraint
Constraint are rules for a table which effect either the column or role. Constraints consist of Not Null, Unique, Primary Key, Foreign Key, Check, Default, Index.
Alter Table
The ALTER TABLE command modifies column definitions and table constraints 'on the fly'. This means existing definitions are extended, changed or deleted or existing data is casted to a different type or existing data is evaluated against the new definitions.[4]
column constraint
The column constraint clause specifies conditions which all values must meet.[5]
Create Table
The CREATE TABLE command is used to create a table in the database. It is not required to write it in Upper case letters, but it is common to do so in DDL.
data type
a data type or simply type is an attribute of data which tells the compiler or interpreter how the programmer intends to use the data.[6]
DDL
DDL (Data Definition Language) refers to the CREATE, ALTER and DROP statements.[7]
DML
DML (Data Manipulation Language) refers to, but is not limited to the INSERT, UPDATE and DELETE statements.[8]
Drop Table
The DROP TABLE command removes the definition and all data of the named table from the database.[9]
ERD (Entity-Relationship Diagram)
A type of flowchart which illustrates how entities such as people, objects, or concepts relates to each other within a system(database).
INSERT INTO statement
Used to insert new rows/records into a table. [10]
foreign key
The FOREIGN KEY condition defines that the column can hold only those values, which are also stored in a different column of (the same or) another table.[11]
Inner Join
The INNER JOIN selects all rows from both participating tables as long as there is a match between the columns. An SQL INNER JOIN is same as JOIN clause, combining rows from two or more tables.[12]
LIKE Clause
The LIKE clause is used to select rows that carry fields that match specific details and parts of character strings. It's a wildcard and there can be different ways to use it.
primary key
The PRIMARY KEY phrase defines that the column acts as the Primary Key of the table. This implies that the column is not allowed to store a NULL value and that the values of all rows are distinct from each other.[13]
SELECT Statement/Command
The SELECT statement/command is used for the user to pull specific data from tables based on the criteria needed.
String Functions
String Functions are functions that are performed on strings. An example is Substring, which extracts a requested portion of the string.[14]
Structured Query Language
Structured Query Language (SQL) is a widely-used programming language for working with relational databases.[15]
UPDATE statement/command
DDL statement that changes data in existing rows either by modifying it or by adding new data. [16]
View statement/command
using the VIEW command creates a virtual table. [17]
Select statement/command
Allows the user to extract data from tables based on specific criteria. [18]

Review Questions[edit | edit source]

Enable JavaScript to hide answers.
Click on a question to see the answer.
  1. An Access database filename extension is _____.
    An Access database filename extension is a .accdb, separated from the base filename by a dot or space, used to indicate the file's content format or usage.
  2. Setting the _________ will define what the column can hold.
    Setting the data type will define what the column can hold.
  3. Database software is _____.
    Database software, also known as database management systems (DBMSs), are specially designed applications that interact with the user, other applications, and the database itself to capture and analyze data.[19]
  4. Each column in an Access table datasheet represents a ____.
    Each column in an Access table datasheet represents a Field.
  5. True or False: A table can have more than one primary key.
    True, this is called a composite primary key.
  6. True or False: A table can have more than one foreign key.
    True, there is no limit to the number of foreign keys.
  7. True or False: SQL Server Express and SSMS are created by Microsoft.
    True, they did create both programs.
  8. Designating a ____ ____ restricts the information entered in a field and ensures a uniform look for that data within the database.
    Input Mask.
  9. {{review question |In order to remove an index from a table, you must run a ____ statement. |In order to remove an index from a table, you must run a drop statement.
  10. True or False: You can only write your SQL code in uppercase.
    False, you can write your SQL code in lower case, but this is not recommended.
  11. True or False: DML refers to the CREATE, ALTER and DROP statements.
    False: DML refers to the INSERT, UPDATE and DELETE statements.
  12. True or False: Using a '*' is the same as using '%' in SQL.
    False: '*' are used as wildcards and '%' are used when part of a value is known.
  13. ____ ______ words are represented by lower case letters.
    User Defined words are represented by lower case letters.
  14. An _____ _____ connects two tables on a column with the same data type
    inner join
  15. True or False: Constraints consists of Unique, Primary Key, Foreign Key, Null, Check, Default and Index.
    False: Constraints consists of Unique, Primary Key, Foreign Key, "Not Null," Check, Default, and Index.
  16. True or False: DML helps with creating queries, displaying them, formatting them, and editing them.
    False: DML is for manipulating data in the table, inserting, deleting, etc.

See Also[edit | edit source]

References[edit | edit source]