55 lines
3 KiB
Markdown
55 lines
3 KiB
Markdown
# 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
|
|
|
|
|