Hot answers tagged

47

Either your professor made a mistake or you misunderstood what she said. In the context of relational DBMSes, as implemented by various vendors, the aggregate function COUNT(<expression>) returns the number of non-NULL values of <expression> in the result set (or a group). There is a special case of COUNT(*), which returns the number of rows in ...


38

COUNT does count duplicates in all DBMS I'm aware of, but. Is there any reason for a professor to teach this behaviour Yes, there is a reason. In the original relational theory (that underlies all modern relational DBMSes) relation is a set in mathematical sense of this word. That means that no relation can contain duplicates at all, including all ...


13

If your professor is talking about SQL, the statement is wrong. COUNT(x) will return the number of rows where x IS NOT NULL including duplicates. COUNT(*) or COUNT([constant]) is a special case that will count the rows, even those where every column is NULL. However, duplicates are always counted, unless you specify COUNT(distinct x). Example: with t(x,y) ...


7

As a rule of thumb: If your data is well structured, well known (in advance) and of a limited size per entry (no mega BLOBs), relational databases are really good at storing it. Even if you don't use the advanced indexing features. Managing space, especially empty space in data files, is a very hard problem to solve. Relational databases have been dealing ...


6

The problem is not simple as it involves 2 entities/tables (Employee and Project) that have 2 relationships between them. One is through the Department (a project belongs to a department, an employee belongs to a department) and the other through Onproject (which shows that an employee has started working on a project). Moreover, the answer needs a ...


6

I want to understand if there is a possibility that transaction being run via one application can affect the ones from an independent other application. Directly, no. Each transaction is by definition is a "single unit of work" and is local to itself. That is, my transaction cannot become part of your transaction. I can't explicitly tell yours to roll ...


5

ORA-01102 cannot mount database in EXCLUSIVE mode Cause: Some other instance has the database mounted exclusive or shared. Action: Shut down the other instance or mount in a compatible mode. Find whether you have any running Oracle process. $ ps -ef | grep smon Kill the process then try to start again. Since I dont have the procedure which you ...


5

Does the term page have the same meaning in DBMS and in OS? Generically, yes. The term PAGE is a generic for the smallest allocate-able unit of space. In SQL Server, for example, pages are a fixed size and have historically been 4k and 8k, depending on version (not counting Large Pages which are virtual memory and not database structures) and in Windows ...


5

Yes. IBM i, formerly known as AS/400, is a platform with the operating system that has a relational DBMS as its integral part. Indeed, Db2 for i is in a way an alternative access method to the IBM i file system.


4

If you compare any two authorative references for a single subject they will employ different words even though they convey the same meaning. This is just the nature of English - it has a very rich and overlapping vocabulary, accumulated from many sources over centuries. Looking at the examples listed in the question they all contain the concepts of data, ...


4

Basics: The 1st row of the CSV file has column names of defined format. The PROGRAM clause of COPY and GET DIAGNOSTICS after COPY require Postgres 9.3+. format() requires Postgres 9.1+ This works with pure standard Postgres - except for the head command that the shell is expected to provide. For Windows versions consider: How to do what head, tail, more, ...


3

The normal forms are specified in such a way that for any normal form, the model is also meets the criteria for the lower numbered normal forms. So, yes if your model is in 3rd normal form, it is also in 2nd normal form and 1st normal form. When normalizing a model, you normally work at resolving one normal form before the next. This makes normalization ...


3

You can use the following: The following query will generate all the keys which you have to drop auto, then simply run: SELECT CONCAT ('alter table ', TABLE_SCHEMA, '.', TABLE_NAME, ' DROP FOREIGN KEY ', CONSTRAINT_NAME, ';') FROM INFORMATION_SCHEMA....


3

This is how it works in MS SQL Server COUNT(*) returns the number of items in a group. This includes NULL values and duplicates. COUNT(ALL expression) evaluates expression for each row in a group and returns the number of nonnull values. COUNT(DISTINCT expression) evaluates expression for each row in a group and returns the number of ...


3

This is not a challenge for one, modern RDBMS. You will realize you do not want to have one database per store the first time executive management asks for "How many iPhone 7s do we have on hand across all stores ahead of the new Apple launch?" I have seen retail systems managing several hundred thousand SKUs across several hundred stores. The master ...


3

1) select TOP (1) ename,salary from Employee where classification='4' order by salary desc 2) select did,dname from Department where exists (select 1 from Employee group by did,classification having count(*) = 2 and classification = '3' and Department.did = Employee.did) 3) with cte as( select did,byear,budget,LAG(byear) OVER (partition by ...


3

If your code does not affect Isolation Level of your queries, non of your apps can make queries to Rollback. They can only Suspend each other. Use a SQL Server Profiler to track your app and see what makes your queries Rollback. To be added, what queries they run when the Rollback happens? I mean a SELECT, INSERT, DELETE or UPDATE?


3

Googling a bit I found another operating system called TabulaROSA. Designed by MIT it works on 32k core supercomputers, if you or your company can afford a system like that, it could be a good option. Many overlaps between database systems and the functions required for an OS on a cluster, In the case of TabulaROSA, however, the functions are managed ...


2

Not with row-oriented storage, where the column values are stored together per rows, which is not ideal for starting with projection. Lets say Table1 has 30 columns, but you need data only from 2 (username, usertype). If username and usertype columns are not indexed, the DBMS has to read the full table with all rows (thus read all columns). In this case ...


2

Instead of using simply text based replace, you can do this with the metadata API, which is a more robust solution. Lets say you have a table X.TABLE1: create table x.table1 (id number, name varchar2(100), primary key(id)); You want to recreate it as Y.TABLE2, you could do the following: set serveroutput on declare metadata_handle number; ...


2

Just for a start: Usually, this should become clear from normalizing the data. You need a separate table for each entity. And you require an intermediate table for many to many (m:n) relations. With hotel and room/reservation, it's unlikely that a specific room/reservation relates to more than a single hotel. For meals, you need to decide, what exactly ...


2

Take a look here. It's a tool which genuinely tries to compare like with like in terms of database benchmarking!


2

Every R (relational) DBMS I've used has. MySQL is peculiar in so many ways I struggle to put it head to head with any of the "normal" RDBMS for anything other than a basic data dump for a simple application. There are plenty of DBs that are not relational that don't have the concept. As far as I know though, there are none other of either sort that ...


2

Thanks for JSapkato's response. This made me thinking to find that elusive database instance that prevented me from mounting my database. It turns out that I made a mistake and used the 1st DB's control file name in the 2nd DB's pfile. I changed the control file names according to the "real" ones in ASM. Now I do not get this "exclusive" mounting errors. ...


2

Since the documentation tells you exactly what precautions to take, I was expecting this to actually work...or else, what's the point of that guide in the official documentation? If this couldn't be done, why would it tell you how to do it in the first place? Most importantly, do you know how to make this work, using MySQL? You said something that ...


2

You need to add column name along with table name. CREATE TABLE Department (dept_name varchar(20), building varchar(15), budget numeric(12,2), primary key(dept_name)); CREATE TABLE Course (course_id varchar(7), title varchar(50), dept_name varchar(20),credits numeric(2,0), primary key(course_id), foreign key(dept_name) references ...


2

This is for SQLServer, databases and schemas are different in other DBMS like Oracle. A database and a schema are two different concepts. A database can contain multiple schemas, for example the default schema 'dbo' You can address a table by specifiying database.schema.tablename. Schemas are often used for security reasons, cause you can have one ...


1

If the table had looked like this, | product | |id | length | code | |-------------------| | 1 | 11 | X00 | | 2 | 11 | C02 | | 3 | null | A31 | you could expect the query to return 2, at least in Oracle DB, as nulls are not counted. Duplicates are however counted just fine.


1

https://docs.oracle.com/cd/B28359_01/server.111/b28310/create003.htm#ADMIN11073 To create a database first you must connect to an instance. SGA is a part of an instance so it is created before the database, thus A is a wrong answer.


1

A teacher can teach the same class several times in the same day and many times over years. A student can re-take a class. But, a teacher can only teach 1 class at a time and a student can only take one class at a time. I would add class instance and class enrollment tables as your junction tables. Your initial attempt isn't that far off from this: 1....


Only top voted, non community-wiki answers of a minimum length are eligible