SQL Server for Database Administrators

Data is one of your business’s most valuable commodities. In this post you’ll learn the key skills you need to set-up, design, maintain, and monitor your team’s database. Dive in and learn the key SQL Server skills you need to securely manage access and monitor database performance using SQL server.

Query information_schema with SELECT

information_schema is a meta-database that holds information about your current database. information_schema has multiple tables you can query with the known SELECT * FROM syntax:

• tables: information about all tables in your current database
• columns: information about all columns in all of the tables in your current database

Get information on all table names in the current database, while limiting your query to the 'public' table_schema.

-- Query the right table in information_schema
SELECT table_name 
FROM information_schema.tables
-- Specify the correct table_schema value
WHERE table_schema = 'public';

The 'public' schema holds information about user-defined tables and databases. The other types of table_schema hold system information.

CREATE your first few TABLEs

The syntax for creating simple tables is as follows:

CREATE TABLE table_name (
 column_a data_type,
 column_b data_type,
 column_c data_type
);

Attention: Table and columns names, as well as data types, don’t need to be surrounded by quotation marks.

-- Create a table for the professors entity type
CREATE TABLE professors (
 firstname text,
 lastname text
);

-- Print the contents of this table
SELECT * 
FROM professors

-- Create a table for the universities entity type
CREATE TABLE universities (
 university_shortname text,
 university text,
 university_city text
);

-- Print the contents of this table
SELECT * 
FROM universities;

ADD a COLUMN with ALTER TABLE

However, adding columns to existing tables is easy, especially if they’re still empty. To add columns you can use the following SQL query:

ALTER TABLE table_name
ADD COLUMN column_name data_type;

-- Add the university_shortname column
ALTER TABLE professors
ADD COLUMN university_shortname text;

-- Print the contents of this table
SELECT * 
FROM professors

RENAME and DROP COLUMNs in affiliations

Use the following queries:

• To rename columns:

ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;

• To delete columns:

ALTER TABLE table_name
DROP COLUMN column_name;

• Examples:

-- Rename the organisation column
ALTER TABLE affiliations
RENAME COLUMN  organisation TO organization;

-- Delete the university_shortname column
ALTER TABLE affiliations
DROP COLUMN university_shortname;

Migrate data with INSERT INTO SELECT DISTINCT

Use the following pattern to migrate data into new tables:

INSERT INTO ... 
SELECT DISTINCT ... 
FROM ...;

It can be broken up into two parts:

• First part:

SELECT DISTINCT column_name1, column_name2, ... 
FROM table_a;

• Second part:

INSERT INTO table_b ...;

• Examples:

-- Insert unique professors into the new table
INSERT INTO professors 
SELECT DISTINCT firstname, lastname, university_shortname 
FROM university_professors;

-- Doublecheck the contents of professors
SELECT * 
FROM professors;

-- Insert unique affiliations into the new table
INSERT INTO affiliations 
SELECT DISTINCT  firstname, lastname, function, organization 
FROM university_professors;

-- Doublecheck the contents of affiliations
SELECT * 
FROM affiliations;

Delete tables with DROP TABLE

For table deletion, you can use the simple command:

DROP TABLE table_name;