ChamplainTechJournals/db-security-sec300/week1.md

Week1

Summary: Set up mysql, basic queries

Install mysql-server:

• sudo apt-get install mysql-server

• change bind address via /etc/mysql/mysql.conf.d/mysqld.cnf
• remember! sudo systemctl restart mysql
• default password is found in /etc/mysql/debian.cnf
• first login: sudo mysql -u root -p
• show current users/DBs: USE mysql;, SELECT User, Host FROM mysql.user;, SHOW DATABASES;
• create registration DB: CREATE DATABASE registration;
• USE registration;
• create requests table:

CREATE TABLE requests(
id INT unsigned NOT NULL AUTO_INCREMENT,
fname VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL,
rdate DATE NOT NULL,
uid VARCHAR(15) NOT NULL,
PRIMARY KEY (id)
);

• show table: DESCRIBE requests;

• add data to table: INSERT INTO requests (fname, email, rdate, uid) VALUES ( 'dummy', 'dummy@dummy.edu', '2024-11-12', 'nsk31fhenfJF024');

HW

1: Write an SQL query that displays name and birth of cats whose names are Siggy

2: Write an SQL query that displays name and birth of cats whose owners names are starting with the letter 'F'

3: Write an SQL query that displays the cat names, their owners names, and the birth of cats in single table for cats born in year 2020

4: Write an SQL query that displays names of owners who has no cats