How would you design an SQL query to a database without using ORM?

To select data from the users table where the is_active field is true and role is 'admin':

SELECT id, username, email
FROM users
WHERE is_active = TRUE AND role = 'admin';

To insert a new record into the products table:

INSERT INTO products (name, price, category_id)
VALUES ('New Product', 19.99, 10);

To update a record in the orders table, setting the status to 'shipped' for the order with id = 100:

UPDATE orders
SET status = 'shipped'
WHERE id = 100;

To delete a record from the comments table by id:

DELETE FROM comments
WHERE id = 500;

For a basic join of the users and orders tables on user_id:

SELECT u.username, o.order_date, o.total_amount
FROM users AS u
JOIN orders AS o ON u.id = o.user_id;

When designing, it is important to consider:

• Security: Use parameterized queries to prevent SQL injection.
• Performance: Optimize queries using indexes, avoid SELECT *, and check execution plans.
• Readability: Format queries, use table and field aliases.
• Testing: Test queries in different scenarios and data volumes.

An example of a parameterized query (conceptually, implementation depends on language and driver):

// Example for selecting a user by ID
SELECT id, username FROM users WHERE id = ?;