• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号

ga-wdi-exercises/library_sql: Exercise to practice working with SQL, Databases a ...

原作者: [db:作者] 来自: 网络 收藏 邀请

开源软件名称(OpenSource Name):

ga-wdi-exercises/library_sql

开源软件地址(OpenSource Url):

https://github.com/ga-wdi-exercises/library_sql

开源编程语言(OpenSource Language):

TSQL 100.0%

开源软件介绍(OpenSource Introduction):

Library DB

Get Started

Clone down this repo and open its contents in VSCode. Then read through and follow the steps below.

Creating Our Database

One of two ways!

From within your terminal environment:

$ createdb library

Or

# launch the psql cli
$ psql

# create the db using a SQL command
> CREATE DATABASE library;

Inspecting The Schema

Look critically at each line of the provided schema.sql file. Here's how one row breaks down...

id SERIAL PRIMARY KEY

  • id: column name, how we will refer to this column
  • SERIAL: the data type (similar to integer or string). It's a special datatype for unique identifier columns, which the db auto-increments.
  • PRIMARY KEY: a special constraint which indicates a unique identifier for each row

Take a few minutes to research the other rows.

Load The Schema

Load the schema into your database from the command line...

$ psql -d library < schema.sql

This command is also run from your Bash prompt -- not inside psql

Loading A Seed File

We've provided a sql file that adds sample data into our library database.

Load that in so we can practice interacting with our data. Make sure to also look at its contents and see how authors and books are related.

$ psql -d library < seed.sql

Performing CRUD actions with SQL

CRUD stands for the most basic interactions we want to have with any database: Create, Read, Update and Destroy.

The most common SQL commands correspond to these 4 actions...

  • INSERT -> Create a row
  • SELECT -> Read / get information for rows
  • UPDATE -> Update a row
  • DELETE -> Destroy a row

First, enter into the library DB...

$ psql
$ \c library

INSERT

INSERT adds a row to a table...

INSERT INTO authors(name, nationality, birth_year) VALUES ('Adam Bray', 'United States of America', 1985);

SELECT

SELECT returns rows from a table...

-- select all columns from all rows
SELECT * FROM authors;

-- select only some columns, from all rows
SELECT name, birth_year FROM authors;

-- select rows that meet certain criteria
SELECT * FROM authors WHERE name = 'James Baldwin';

UPDATE

UPDATE updates values for one or more rows...

UPDATE authors SET name = 'Adam B.', birth_year = 1986 WHERE name = 'Adam Bray';

DELETE

DELETE removes rows from a table...

DELETE FROM authors WHERE name = 'Adam B.';

End of You Do: Building Our Database


Exercises

There are two exercises:

For each exercise, write your queries in the corresponding .sql file. Then run the file using the terminal:

$ psql -d library < basic_queries.sql



鲜花

握手

雷人

路过

鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
热门推荐
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服(服务时间 9:00~18:00)

在线QQ客服
地址:深圳市南山区西丽大学城创智工业园
电邮:jeky_zhao#qq.com
移动电话:139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap