A customer can have multiple phone numbers e.g. Cell, Work, etc. phoneID in Customer table is unique and points to PhoneID in Phone table. If customer record is deleted, phoneID in Phone table should also be deleted. Do you have any concerns on my design? Is this designed properly? My problem is phoneID in Cu…
Tag: database
What does the specified number mean in a VARCHAR() clause?
Just to clarify, by specifying something like VARCHAR(45) means it can take up to max 45 characters? I remember I heard from someone a few years ago that the number in the parenthesis doesn’t refer to …
Python: Number of rows affected by cursor.execute(“SELECT …)
How can I access the number of rows affected by: Answer Try using fetchone: result will hold a tuple with one element, the value of COUNT(*). So to find the number of rows: Or, if you’d rather do it in one fell swoop: PS. It’s also good practice to use parametrized arguments whenever possible, bec…
SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints
I need to write a query on SQL server to get the list of columns in a particular table, its associated data types (with length) and if they are not null. And I have managed to do this much. But now i also need to get, in the same table, against a column – TRUE if that column is a
Two single-column indexes vs one two-column index in MySQL?
I’m faced with the following and I’m not sure what’s best practice. Consider the following table (which will get large): id PK | giver_id FK | recipient_id FK | date I’m using InnoDB and from what I understand, it creates indices automatically for the two foreign key columns. However, …
Database design for monitoring status of applications’ functionalities
I’m creating the database for monitoring status of applications’ functionalities. The logic is following: Each application has its own, specific list of functionalities that I’m monitoring. Each …
sqlite select with condition on date
I have an sqlite table with Date of Birth. I would like to execute a query to select those records where the age is more than 30. I have tried the following but it doesn’t work: Answer Some thing like this could be used: If you are using Sqlite V3.7.12 or greater Dont use date(dateofbirth) just use date…
Check if role exists in DB?
I want to add users to the same role in more than one database. However the role may or may not be present in each database. How can I check if the role exists in each database and if it does, add users to that role? e.g. IF role exists BEGIN Add user in role END Answer try:
Combining data from different tables to form a news feed
Let’s say I have 2 tables, Message and Product. Whenever a user post a new products or messages, users who subscribe to that particular user will have their feed updated.It is similar to Facebook feed….
How can I select records ONLY from yesterday?
I’ve spent hours searching the web for an answer to this question… Here’s what I currently have: select * from order_header oh where tran_date = sysdate-1