Data definition language (DDL) consists of SQL statements that define database structures. A practical DDL task is designing a table and producing its CREATE TABLE statement from a short workplace requirement. section 10.1
A clear DDL request can ask for both the structure and the SQL statement:
- Describe the table’s purpose, then request suitable columns and a CREATE TABLE statement. section 10.1
- For an application login table, suggested fields include UserID, Username, Email, PasswordHash, and LastLogin. section 10.1
- Review generated SQL before using it; the example avoids plain-text passwords and includes LastLogin for auditing. section 10.1
Table 10.1 Query and object creation prompts
|
Task
|
Prompt
|
Response summary
|
|---|---|---|
|
Query creation
|
Write a T-SQL query to list all tables in my database with a column named
[SSN]. Then provide a
SELECT
statement that would redact the value of the
[SSN] column.
|
A
SELECT statement is written to query
INFORMATION_SCHEMA.COLUMNS for matching column names. The database, schema, and table names are included columns. A
SELECT statement is also created that uses the
STUFF function to redact the first five digits of the Social Security number, leaving the last four digits visible.
|
|
Table creation
|
I am being asked by a developer to create a new SQL Server database table named [
Users] that will hold login information for application users. Please design a suitable table and provide the Create Table statement.
|
A
CREATE TABLE statement is crafted, consisting of
UserID,
Username,
Email,
PasswordHash, and other relevant information useful for auditing the user account.
|
|
Procedure creation
|
Create a T-SQL stored procedure to rebuild fragmented indexes on database tables. Include input parameters that are helpful to allow the procedure to target only specific tables and indexes that need to be rebuilt.
|
A
CREATE PROCEDURE statement is provided that accepts the database name, minimum page count and fragmentation levels, and table and index name pattern inputs. It also provides an example of how to call the procedure.
|
|
Trigger creation
|
Create a SQL Server trigger that will send an email whenever a new database is restored or created.
|
The chatbot first provides a link to configuring database mail as a prerequisite. It then writes a
CREATE TRIGGER statement for a server-level trigger, which fires on the
CREATE and
RESTORE commands. The database name and create date are added to an email message and sent using
sp_send_dbmail.
|
|
Function creation
|
Create a SQL Server scalar function that takes a date of birth as an input parameter and returns the age of a person in years. The function should handle leap years and should return an integer. Provide example usage of the function.
|
A new function is created that uses the
DATEDIFF function to calculate an age, accounting for leap years. If the person’s birthday has not yet passed, it subtracts 1 year from the result.
|
Compare prompts for creating tables and other database objects, including the requested output and response summary. section 10.1
