search

roberthsu2003 / __2024_09_22_chihlee_sql__

2024_09_22 致理 113下新北 python 程式設計與SQL資料庫整合應用班
MIT License
30 stars 0 forks source link

請匯入dvdrental.zip(請解壓縮),並執行以下動作 #3

Open roberthsu2003 opened 2 weeks ago

roberthsu2003 commented 2 weeks ago

使用dvd_rental資料庫內的資料表customer

截圖 2024-10-07 晚上9 20 46

first_name為Jamie

first_name是Jamie,同時last_name是Rice

last_name是Rodriquez 或者 first_name是Adam

取出first_name是Ann,Anne,Annie

取出字串開頭是Ann的first_name

取出first_name第1個字元是A,同時first_name的字元長度是3到5的資料

取出first_name,前3字為Bra,但last_name不是Motley

roberthsu2003 commented 1 week ago

題目不完整,已經更新

djlee54168 commented 1 week ago

● a09李定洲繳交作業

DROP TABLE IF EXISTS customer;

CREATE TABLE IF NOT EXISTS customer(
    customer_id int,
    store_id int,
    first_name VARCHAR(20),
    last_name VARCHAR(20),
    email VARCHAR(100),
    address_id int,
    activebool VARCHAR(10),
    create_date date,
    last_update timestamp,
    active boolean
    );

SELECT * 
FROM customer;

SELECT *
FROM customer
WHERE first_name='Jamie';
SELECT *
FROM customer
WHERE first_name='Jamie' and last_name='Rice';

SELECT *
FROM customer
WHERE last_name='Rodriquez' or first_name='Adam';

SELECT *
FROM customer
WHERE first_name='Ann' or first_name='Anne' or first_name='Annie';

SELECT *
FROM customer
WHERE first_name like 'Ann%';

SELECT *
FROM customer
WHERE first_name like 'A%' and (length(first_name)>=3 and length(first_name)<=5);

SELECT *
FROM customer
WHERE first_name like 'Bra%' and last_name!='Motley';

first_name為Jamie

image

first_name是Jamie,同時last_name是Rice

image

last_name是Rodriquez 或first_name是Adam

image

取出first_name是Ann,Anne,Annie

image

取出字符串開頭是Ann的first_name

image

取出first_name第1個字符是A,同時first_name的字符長度是3到5的資料

image

取出first_name,前3字為Bra,但last_name不是Motley

image

Ryjjja commented 1 week ago

first_name為Jamie

SELECT * FROM customer WHERE first_name = 'Jamie'; 1

·

first_name是Jamie,同時last_name是Rice

SELECT * FROM customer WHERE first_name = 'Jamie' AND last_name = 'Rice';

2

·

last_name是Rodriquez 或者 first_name是Adam

SELECT * FROM customer WHERE last_name ='Rodriquez' OR first_name = 'Adam';

3

·

取出first_name是Ann,Anne,Annie

SELECT * FROM customer WHERE first_name IN ('Ann', 'Anne', 'Annie');

4

·

取出字串開頭是Ann的first_name

SELECT * FROM customer WHERE first_name like 'Ann%';

5

·

取出first_name第1個字元是A,同時first_name的字元長度是3到5的資料

SELECT * FROM customer WHERE first_name like 'A%' AND LENGTH(first_name) BETWEEN 3 AND 5;

6

·

取出first_name,前3字為Bra,但last_name不是Motley

SELECT * FROM customer WHERE first_name like 'Bra%' AND last_name <> 'Motley';

7
Jojojojo945 commented 1 week ago 
DROP TABLE IF EXISTS customer;

CREATE TABLE IF NOT EXISTS customer(
    customer_id int,
    store_id int,
    first_name VARCHAR(20),
    last_name VARCHAR(20),
    email VARCHAR(100),
    address_id int,
    activebool VARCHAR(10),
    create_date date,
    last_update timestamp,
    active boolean
    );

SELECT *
FROM customer;

first_name為Jamie

SELECT *
FROM customer
WHERE first_name = 'Jamie';
image

first_name是Jamie,同時last_name是Rice

SELECT *
FROM customer
WHERE first_name = 'Jamie' AND last_name = 'Rice';
image

last_name是Rodriquez 或者 first_name是Adam

SELECT *
FROM customer
WHERE last_name = 'Rodriquez' OR first_name = 'Adam';
image

取出first_name是Ann,Anne,Annie

SELECT *
FROM customer
WHERE first_name = 'Ann' OR first_name = 'Anne' OR first_name = 'Annie';
image

取出字串開頭是Ann的first_name 

SELECT *
FROM customer
WHERE first_name LIKE 'Ann%';
image

取出first_name第1個字元是A,同時first_name的字元長度是3到5的資料 

SELECT *
FROM customer
WHERE first_name LIKE 'A%' AND LENGTH(first_name) BETWEEN 3 AND 5;
image

取出first_name,前3字為Bra,但last_name不是Motley

FROM customer
WHERE first_name LIKE 'Bra%' AND last_name NOT LIKE 'Motley';
image
Rick-CC commented 1 week ago

程式碼

/* 
   1 先把 dvdrental.zip 解壓縮到本機資料夾中
   2 連上pg4 的 server , 右鍵使用 resotre 選擇前一步的資料夾 & 恢復上去
   3 reflash Server / Schemas 
   4 查看 table 名為 "customer" 的資料表
     透過 Query Tool 輸入 SELECT * FROM customer
   5 點選 Save file, 檔名可命名為 lesson3_homework
   6 完成以下項目
*/

SELECT * FROM customer;

/* first_name為Jamie -----------------------------------------------------------*/
SELECT *
FROM customer
WHERE first_name = 'Jamie';

/* first_name是Jamie,同時last_name是Rice -------------------------------------*/
SELECT *
FROM customer
WHERE first_name = 'Jamie' AND last_name = 'Rice';

/* last_name是Rodriquez 或者 first_name是Adam -----------------------------*/
SELECT *
FROM customer
WHERE last_name = 'Rodriquez' OR first_name = 'Adam';

/* 取出first_name是Ann，Anne，Annie -----------------------------------------*/
SELECT *
FROM customer
WHERE first_name = 'Ann' OR 
      first_name = 'Anne' OR
      first_name = 'Annie';

/* 取出字串開頭是Ann的first_name -----------------------------------------------*/
SELECT *
FROM customer
WHERE first_name LIKE 'Ann%'
ORDER BY first_name;

/* 取出first_name第1個字元是A,同時first_name的字元長度是3到5的資料 ---------------*/
SELECT *
FROM customer
WHERE first_name LIKE 'A%' AND
      LENGTH(first_name) BETWEEN 3 AND 5;

/* 取出first_name,前3字為Bra,但last_name不是Motley --------------------------*/
SELECT *
FROM customer
WHERE first_name LIKE 'Bra%' AND last_name != 'Motley';

/* end of file */

1. first_name為Jamie

image

2. first_name是Jamie,同時last_name是Rice

image

3. last_name是Rodriquez 或者 first_name是Adam

image

4. 取出first_name是Ann，Anne，Annie

image

5. 取出字串開頭是Ann的first_name

image

6. 取出first_name第1個字元是A,同時first_name的字元長度是3到5的資料

共22筆

image

7. 取出first_name,前3字為Bra,但last_name不是Motley

image

--- End ---

HungA commented 1 week ago 
--first_name為Jamie

SELECT * 
FROM customer
WHERE first_name = 'Jamie';

截圖 2024-10-12 18 54 34

--first_name是Jamie,同時last_name是Rice

SELECT * 
FROM customer
WHERE first_name = 'Jamie' AND last_name = 'Rice';

截圖 2024-10-12 18 56 22

--last_name是Rodriquez 或者 first_name是Adam

SELECT * 
FROM customer
WHERE last_name = 'Rice' OR first_name = 'Adam';

截圖 2024-10-12 18 56 55

--取出first_name是Ann,Anne,Annie

SELECT * 
FROM customer
WHERE first_name IN ('Ann','Anne','Annie');

截圖 2024-10-12 18 57 21

--取出字串開頭是Ann的first_name

SELECT * 
FROM customer
WHERE first_name LIKE 'Ann%' ;

截圖 2024-10-12 18 57 47

--取出first_name第1個字元是A,同時first_name的字元長度是3到5的資料

SELECT * 
FROM customer
WHERE first_name LIKE 'A%' AND LENGTH(first_name) BETWEEN 3 AND 5 ;

截圖 2024-10-12 18 58 21

--取出first_name,前3字為Bra,但last_name不是Motley

SELECT * 
FROM customer
WHERE first_name LIKE 'Bra%' AND last_name <> 'Motley';

截圖 2024-10-12 18 58 43

StellaTsai1006 commented 1 week ago 
/*first_name為Jamie*/
SELECT *
FROM customer
WHERE first_name = 'Jamie';

image

/*first_name是Jamie,同時last_name是Rice*/
SELECT *
FROM customer
WHERE first_name = 'Jamie' AND last_name = 'Rice';

image

/*last_name是Rodriquez 或者 first_name是Adam*/
SELECT *
FROM customer
WHERE last_name = 'Rodriquez' OR first_name = 'Adam';
image 
/*取出first_name是Ann,Anne,Annie*/
SELECT *
FROM customer
WHERE first_name IN ('Ann','Anne','Annie');
image 
/*取出字串開頭是Ann的first_name*/
SELECT *
FROM customer
WHERE first_name LIKE 'Ann%';
image 
/*取出first_name第1個字元是A,同時first_name的字元長度是3到5的資料*/
SELECT *
FROM customer
WHERE first_name LIKE 'A%' AND LENGTH(first_name) BETWEEN 3 AND 5;
image 
/*取出first_name,前3字為Bra,但last_name不是Motley*/
SELECT *
FROM customer
WHERE first_name LIKE 'Bra%' AND last_name != 'Motley';
image
JoannChow1999 commented 1 week ago

first_name為Jamie

first_name是Jamie,同時last_name是Rice

last_name是Rodriquez 或者 first_name是Adam

取出first_name是Ann,Anne,Annie

取出字串開頭是Ann的first_name

取出first_name第1個字元是A,同時first_name的字元長度是3到5的資料

取出first_name,前3字為Bra,但last_name不是Motley

peterlu22 commented 1 week ago

1.first_name為Jamie

SELECT * FROM customer
WHERE first_name = 'Jamie';

Homework_3-1

2.first_name是Jamie,同時last_name是Rice

SELECT * FROM customer
WHERE first_name = 'Jamie' AND last_name = 'Rice';

Homework_3-2

3.last_name是Rodriquez 或者 first_name是Adam

SELECT * FROM customer
WHERE last_name = 'Rodriquez' OR first_name = 'Adam';

Homework_3-3

4.取出first_name是Ann,Anne,Annie

SELECT * FROM customer
WHERE first_name IN ('Ann','Anne','Annie');

Homework_3-4

5.取出字串開頭是Ann的first_name

SELECT * FROM customer
WHERE first_name Like 'Ann%';

Homework_3-5

6.取出first_name第1個字元是A,同時first_name的字元長度是3到5的資料

SELECT * FROM customer
WHERE first_name Like 'A%' AND LENGTH(first_name) BETWEEN '3' AND '5';

Homework_3-6

7.取出first_name,前3字為Bra,但last_name不是Motley

SELECT * FROM customer
WHERE first_name Like 'Bra%' AND last_name != 'Motley';

Homework_3-7

RobertChung0518 commented 1 week ago

1. first_name為Jamie

SELECT *
FROM customer
WHERE first_name = 'Jamie';

image

2. first_name是Jamie,同時last_name是Rice

SELECT *
FROM customer
WHERE first_name = 'Jamie' and last_name = 'Rice';

image

3. last_name是Rodriquez 或者 first_name是Adam

SELECT *
FROM customer
WHERE last_name = 'Rodriquez' or first_name = 'Adam';

image

4. 取出first_name是Ann,Anne,Annie

SELECT *
FROM customer
WHERE first_name = 'Ann' or first_name = 'Anne' or first_name = 'Annie';

image

5. 取出字串開頭是Ann的first_name

SELECT *
FROM customer
WHERE first_name like 'Ann%';

image

6. 取出first_name第1個字元是A,同時first_name的字元長度是3到5的資料

SELECT *
FROM customer
WHERE first_name like 'A%' and LENGTH(first_name)BETWEEN 3 AND 5;

image

7. 取出first_name,前3字為Bra,但last_name不是Motley

SELECT *
FROM customer
WHERE first_name like 'Bra%' and last_name!='Motley';

image

U120471683 commented 1 week ago

'''

  1. first_name為Jamie ''' 1 '''
  2. first_name是Jamie,同時last_name是Rice ''' 2

'''

  1. last_name是Rodriquez 或者 first_name是Adam ''' 3

'''

  1. 取出first_name是Ann，Anne，Annie '''

4

'''

  1. 取出字串開頭是Ann的first_name ''' 5

''' 6.取出first_name第1個字元是A,同時first_name的字元長度是3到5的資料 ''' 6

'''

  1. 取出first_name,前3字為Bra,但last_name不是Motley ''' 7
ZCKAO commented 1 week ago 
SELECT * FROM customer;

SELECT * 
FROM customer
WHERE first_name = 'Jamie';

SELECT * 
FROM customer
WHERE first_name = 'Jamie' and last_name = 'Rice';

SELECT * 
FROM customer
WHERE last_name = 'Rodriquez' or first_name = 'Adam';

SELECT * 
FROM customer
WHERE first_name in ('Ann', 'Anne', 'Annie');

SELECT * 
FROM customer
WHERE first_name like 'Ann%';

SELECT * 
FROM customer
WHERE first_name like 'A%' and (length(first_name) between 3 and 5);

SELECT * 
FROM customer
WHERE first_name like 'Bra%' and last_name != 'Motley';