Как решить задачу по SQL?

Question

[Stepan Sidorov]

Привет.
Вот такое сложное для меня дело: Я хорошо прошел все тесты по программированию, но вот мне пишут на электронную почту и говорят что так то все хорошо, примут на работу, но можешь сделать задачу на базу данных SQL только без ORM?
Я в чистом SQL только сегодня копался :)
Вот задача:

Предположим, что у нас есть некая информационная система по учёту клиентов компании, занимающейся продажей круизов по прудам и озёрам России.
Сейчас в информационной системе существует единственная таблица, содержащая информацию о самих клиентах (ФИО и дата рождения).
Вам необходимо:
1.
Предложить как именно доработать БД таким образом, чтобы система могла хранить информацию о приобретённых клиентами круизах (одной сущности будет достаточно);
2.
С учётом добавления новой сущности в БД, напишите запрос, который отберёт всех клиентов, у которых ещё не было приобретено ни одного круиза.


Я ее так решил:

Работаю с базой данных sqlite3 через python:
# Судья по заданию, таблица клиентов в бузе уже есть
# Поэтому я просто вызову ее по названию "TableClients"

conn = sq.connect("MyDataBase.db")
cursor = sq.cursor(conn)
cursor.execute("ALTER table TableClients add column 'Kruiz' 'int'")
clients_not_kruiz = []#Здесь все люди без круизов
for row in cursor.execute("SELECT rowid, * FROM TableClients ORDER BY artist"):
    print(row[4])#Т.К. именно на этом месте находится число круизов
if row[4] > 0:
    pass
else: 
    clients_not_kruiz.append(row[1]+" "+row[2]+" "+row[3]+" ")# Здесь заполняю список людей без круизов

print(clients_not_kruiz)#Здесь вывожу людей без круизов

Можете помочь пожалуйста?

Answer 1

[Dimonchik]

ну, (2) - то и в ORM все же в базе надо выполнять: select бла бла where num_cruises = 0
а 1) все же надо добавлять таблицу круизов и связь с ней, тогде и (2) усложнится, будет join

Answer 2

[Александр]

Вы опишите сначала существующую таблицу:

import sqlite3
from sqlite3 import Error


def post_sql_query(sql_query):
    with sqlite3.connect(database) as connection:
        cursor = connection.cursor()
        cursor.execute(sql_query)
        result = cursor.fetchall()
        return result

def create_tables():
    users_query = '''CREATE TABLE IF NOT EXISTS USERS 
                        (user_id INTEGER PRIMARY KEY NOT NULL, fullname TEXT, birthday TEXT);'''
    order_query = '''CREATE TABLE IF NOT EXISTS ORDERS
                        (order_id INTEGER PRIMARY KEY NOT NULL, user_id INT, order_date TEXT);'''
    post_sql_query(users_query)
    post_sql_query(order_query)


database = 'MyDataBase.db'
create_tables()

Заполняем таблицы данными. Например как на картинке. Таблицы не связаны внешним ключом, но связаны через user_id.

# пример для первой строки
user_insert_query = "INSERT INTO users(fullname,birthday) VALUES('Сидоров Иван Петрович', '01.02.1970');"
order_insert_query = "INSERT INTO orders(user_id,order_date) VALUES(2,'10.03.2020');"
post_sql_query(user_insert_query)
post_sql_query(order_insert_query)

Далее нужно составить запрос ( тут нужна помощь знатоков, всё что я знаю о sql это ничего)
Но для моего примера можно и без JOIN обойтись, чтобы найти тех ЮЗЕРов, кто не делал заказы.

user_without_orders_query = 'SELECT fullname FROM users WHERE user_id NOT IN (SELECT user_id FROM orders);'
U = post_sql_query(user_without_orders_query)
print(U)

p.s. знатоки напишите плиз запрос с JOIN для данного примера?

Comments

[Stepan Sidorov]

ДА мне же нужно на чистом SQL, без Python

[Александр]

Stepan Sidorov, так Вы сами выше обвязку на python показали. Как вы с Sqlite работаете? если напрямую, то и удалите весь питоновский код и останутся только SQL-выражения.